"""
# Ethereum Classic as a BigQuery Dataset

In preparation for the Ethereum Classic dataset being available on Google BigQuery, we prepared a few awesome queries you can run here on this Kaggle notebook. 

This notebook will go over queries you can run for Ethereum Classic, including getting top rich list, hashrate analysis, and daily gini coefficient measurement. Some queries will try to compare with other blockchain networks.

We will also be plotting our queries with [Plotly](https://plot.ly/), which we will be installing in this notebook.
"""
import numpy as np
import pandas as pd
import os
from google.cloud import bigquery
!pip install plotly
from plotly.offline import download_plotlyjs, init_notebook_mode, plot, iplot
init_notebook_mode(connected=True)
client = bigquery.Client()
ethereum_classic_dataset_ref = client.dataset('crypto_ethereum_classic', project='bigquery-public-data')
"""
## Top Miners By Rewards in the Last 30 Days

Here, we try to find out who are the top miners by the address of the block mined in the last 30 days. We will run the following query as a string in python through the BigQuery client.
```
WITH mined_block AS (
  SELECT miner, DATE(timestamp)
  FROM `bigquery-public-data.ethereum_classic_blockchain.blocks` 
  WHERE DATE(timestamp) > DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)
  ORDER BY miner ASC)
SELECT miner, COUNT(miner) AS total_block_reward 
FROM mined_block 
GROUP BY miner 
ORDER BY total_block_reward ASC
```

Let's run it.
"""
query = """
WITH mined_block AS (
  SELECT miner, DATE(timestamp)
  FROM `bigquery-public-data.crypto_ethereum_classic.blocks` 
  WHERE DATE(timestamp) > DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)
  ORDER BY miner ASC)
SELECT miner, COUNT(miner) AS total_block_reward 
FROM mined_block 
GROUP BY miner 
ORDER BY total_block_reward DESC
LIMIT 10
"""

query_job = client.query(query)
iterator = query_job.result()
rows = list(iterator)
# Transform the rows into a nice pandas dataframe
top_miners = pd.DataFrame(data=[list(x.values()) for x in rows], columns=list(rows[0].keys()))
# Look at the first 10 headlines
top_miners.head(10)
"""
## Plotly Library for Plotting
In this notebook, we will be using [Plotly](https://plot.ly/) for plotting our charts. You can sign up for a free account to get your API key in order to generate the charts if you choose to run this notebook on your own.

Let's start by plotting the top miners by their block reward as a pie chart.
"""
from plotly.offline import download_plotlyjs, init_notebook_mode, plot, iplot
import plotly.graph_objs as go
labels = top_miners['miner']
values = top_miners['total_block_reward']

trace = go.Pie(labels=labels, values=values)

iplot([trace])
"""
## Top Miners By Block Rewards All Time

Now, let's make it more interesting and plot the total rewards of everyone who has ever mined Ethereum Classic from the genesis block.

We will limit it to just miners who's daily block rewards are greater than 100. This allows us to save on computing and plotting the traces in this Kaggle notebook.

We can use the query here:
```
#standardSQL
-- MIT License
-- Copyright (c) 2019 Yaz Khoury, yaz.khoury@gmail.com

SELECT miner, 
    DATE(timestamp) as date,
    COUNT(miner) as total_block_reward
FROM `bigquery-public-data.crypto_ethereum_classic.blocks` 
GROUP BY miner, date
HAVING COUNT(miner) > 100
ORDER BY date, COUNT(miner) ASC
```
"""
query = """
#standardSQL
-- MIT License
-- Copyright (c) 2019 Yaz Khoury, yaz.khoury@gmail.com

SELECT miner, 
    DATE(timestamp) as date,
    COUNT(miner) as total_block_reward
FROM `bigquery-public-data.crypto_ethereum_classic.blocks` 
GROUP BY miner, date
HAVING COUNT(miner) > 100
ORDER BY date, COUNT(miner) ASC
"""
query_job = client.query(query)
iterator = query_job.result()
rows = list(iterator)
# Transform the rows into a nice pandas dataframe
top_miners_by_date = pd.DataFrame(data=[list(x.values()) for x in rows], columns=list(rows[0].keys()))
top_miners_by_date.head(10)
date_series = top_miners_by_date['date'].unique()
date_series
traces = []
miner_series = top_miners_by_date['miner'].unique()

for index, miner in enumerate(miner_series):
    miner_reward_by_date = top_miners_by_date.loc[top_miners_by_date['miner'] == miner]
    miner_reward = miner_reward_by_date['total_block_reward']
    miner_date = miner_reward_by_date['date']
    trace = dict(
        x=miner_date,
        y=miner_reward,
        mode='lines',
        stackgroup='one'
    )
    traces.append(trace)
fig = dict(data=traces)

iplot(fig)
"""
## Daily Gini Coefficient of Ethereum Classic Mining Rewards By Miners

Now, we shall compute the [Gini coefficient](https://en.wikipedia.org/wiki/Gini_coefficient).

The Gini coefficient is a statistical measure of distribution used to measure income or wealth distribution among a population. From the [Investopedia article](https://www.investopedia.com/terms/g/gini-index.asp):
> A country in which every resident has the same income would have an income Gini coefficient of 0. A country in which one resident earned all the income, while everyone else earned nothing, would have an income Gini coefficient of 1.

Here, we are calculating the daily block reward distribution among addresses based on which mining address received a block daily.

The query uses what was constructed earlier and borrows from the implementation of the [Daily Balance Gini query](https://medium.com/google-cloud/calculating-gini-coefficient-in-bigquery-3bc162c82168) here. 

Also, just for fun, we shall calculate the [Simple Moving Average](https://www.investopedia.com/terms/s/sma.asp) of the Gini, using a 7 and 30 day windows. Simple Moving Average of SMA takes a set of values and their time periods, averages out a sum of the values divided by the chosen time window. 

The query value for those here will be `gini_sma_7` and `gini_sma_30`.

Please not that this only calculates the gini of those miners who earned more than 1% of the block rewards in a day, otherwise, it'll always go to 1 due to many mining just 1 block.
"""
query = """
#standardSQL
-- MIT License
-- Copyright (c) 2019 Yaz Khoury, yaz.khoury@gmail.com

WITH total_reward_book AS (
  SELECT miner, 
    DATE(timestamp) as date,
    COUNT(miner) as total_block_reward
  FROM `bigquery-public-data.crypto_ethereum_classic.blocks` 
  GROUP BY miner, date
  HAVING COUNT(miner) > 100
),
total_reward_book_by_date AS (
 SELECT date, 
        miner AS address, 
        SUM(total_block_reward / POWER(10,0)) AS value
  FROM total_reward_book
  GROUP BY miner, date
),
daily_rewards_with_gaps AS (
  SELECT
    address, 
    date,
    SUM(value) OVER (PARTITION BY ADDRESS ORDER BY date) AS block_rewards,
    LEAD(date, 1, CURRENT_DATE()) OVER (PARTITION BY ADDRESS ORDER BY date) AS next_date
  FROM total_reward_book_by_date
),
calendar AS (
  SELECT date 
  FROM UNNEST(GENERATE_DATE_ARRAY('2015-07-30', CURRENT_DATE())) AS date
),
daily_rewards AS (
  SELECT address, 
    calendar.date, 
    block_rewards
  FROM daily_rewards_with_gaps
  JOIN calendar ON daily_rewards_with_gaps.date <= calendar.date 
  AND calendar.date < daily_rewards_with_gaps.next_date
),
supply AS (
  SELECT date,
    SUM(block_rewards) AS total_rewards
  FROM daily_rewards
  GROUP BY date
),
ranked_daily_rewards AS (
  SELECT daily_rewards.date AS date,
    block_rewards,
    ROW_NUMBER() OVER (PARTITION BY daily_rewards.date ORDER BY block_rewards DESC) AS rank
  FROM daily_rewards
  JOIN supply ON daily_rewards.date = supply.date
  WHERE SAFE_DIVIDE(block_rewards, total_rewards) >= 0.01
  ORDER BY block_rewards DESC
),
daily_gini AS (
  SELECT date,
    -- (1 − 2B) https://en.wikipedia.org/wiki/Gini_coefficient
    1 - 2 * SUM((block_rewards * (rank - 1) + block_rewards / 2)) / COUNT(*) / SUM(block_rewards) AS gini
  FROM ranked_daily_rewards
  GROUP BY DATE
)
SELECT date,
  gini,
  AVG(gini) OVER (ORDER BY date ASC ROWS 7 PRECEDING) AS gini_sma_7,
  AVG(gini) OVER (ORDER BY date ASC ROWS 30 PRECEDING) AS gini_sma_30
FROM daily_gini
ORDER BY date ASC
"""

query_job = client.query(query)
iterator = query_job.result()
rows = list(iterator)
# Transform the rows into a nice pandas dataframe
mining_reward_gini_by_date = pd.DataFrame(data=[list(x.values()) for x in rows], columns=list(rows[0].keys()))
mining_reward_gini_by_date.head(10)
traces = []
x = mining_reward_gini_by_date['date']
gini_list = ['gini', 'gini_sma_7', 'gini_sma_30']
for gini in gini_list:
    y = mining_reward_gini_by_date[gini]
    trace = dict(
        x=x,
        y=y,
        hoverinfo=f'{gini}',
        mode='lines'
    )
    traces.append(trace)
fig = dict(data=traces)

iplot(fig, validate=False)
"""
## Latest Daily Balance of Ethereum Classic (Top 20 Rich List)

This next query, adapted from this [Medium post by Evgeny Medvedev](https://medium.com/google-cloud/how-to-query-balances-for-all-ethereum-addresses-in-bigquery-fb594e4034a7) will get us the latest daily balance for Ethereum Classic.

We can order it by balance, getting us a nice rich list we can plot.
"""
query = """
with double_entry_book as (
    -- debits
    select to_address as address, value as value
    from `bigquery-public-data.crypto_ethereum_classic.traces`
    where to_address is not null
    and status = 1
    and (call_type not in ('delegatecall', 'callcode', 'staticcall') or call_type is null)
    union all
    -- credits
    select from_address as address, -value as value
    from `bigquery-public-data.crypto_ethereum_classic.traces`
    where from_address is not null
    and status = 1
    and (call_type not in ('delegatecall', 'callcode', 'staticcall') or call_type is null)
    union all
    -- transaction fees debits
    select miner as address, sum(cast(receipt_gas_used as numeric) * cast(gas_price as numeric)) as value
    from `bigquery-public-data.crypto_ethereum_classic.transactions` as transactions
    join `bigquery-public-data.crypto_ethereum_classic.blocks` as blocks on blocks.number = transactions.block_number
    group by blocks.miner
    union all
    -- transaction fees credits
    select from_address as address, -(cast(receipt_gas_used as numeric) * cast(gas_price as numeric)) as value
    from `bigquery-public-data.crypto_ethereum_classic.transactions`
)
select address, 
sum(value) / 1000000000 as balance
from double_entry_book
group by address
order by balance desc
limit 20
"""

query_job = client.query(query)
iterator = query_job.result()
rows = list(iterator)
# Transform the rows into a nice pandas dataframe
top_address_rich_list = pd.DataFrame(data=[list(x.values()) for x in rows], columns=list(rows[0].keys()))
top_address_rich_list.head(10)
labels = top_address_rich_list['address']
values = top_address_rich_list['balance']

trace = go.Pie(labels=labels, values=values)

iplot([trace])
"""
## Daily Top Balance Gini Coefficient

Now, we will try getting daily top rich list from the genesis until now and then calculate the gini coefficient of the rich list.

In this context, the gini coefficient will be a measure of income inequality among wallet addresses based on how much ether balance is in each wallet. Of course, this assumes 1 person = 1 wallet, but a person can have multiple wallets. It will also query for top 10k addresses to be used in gini analysis. That will include exchange account balances, which we don't take into account eliminating from the dataset.

The query was written by from **[Evegeny Medvedev and Allen Day for this Google Blog Post](https://cloud.google.com/blog/products/data-analytics/introducing-six-new-cryptocurrencies-in-bigquery-public-datasets-and-how-to-analyze-them)**. I added some further analysis towards the end to measure the Simple Moving Average of the Gini for the past 7 and 30 days.
"""
query = """
with 
double_entry_book as (
    -- debits
    select to_address as address, value as value, block_timestamp
    from `bigquery-public-data.crypto_ethereum_classic.traces`
    where to_address is not null
    and status = 1
    and (call_type not in ('delegatecall', 'callcode', 'staticcall') or call_type is null)
    union all
    -- credits
    select from_address as address, -value as value, block_timestamp
    from `bigquery-public-data.crypto_ethereum_classic.traces`
    where from_address is not null
    and status = 1
    and (call_type not in ('delegatecall', 'callcode', 'staticcall') or call_type is null)
    union all
    -- transaction fees debits
    select miner as address, sum(cast(receipt_gas_used as numeric) * cast(gas_price as numeric)) as value, block_timestamp
    from `bigquery-public-data.crypto_ethereum_classic.transactions` as transactions
    join `bigquery-public-data.crypto_ethereum_classic.blocks` as blocks on blocks.number = transactions.block_number
    group by blocks.miner, block_timestamp
    union all
    -- transaction fees credits
    select from_address as address, -(cast(receipt_gas_used as numeric) * cast(gas_price as numeric)) as value, block_timestamp
    from `bigquery-public-data.crypto_ethereum_classic.transactions`
),
double_entry_book_by_date as (
    select 
        date(block_timestamp) as date, 
        address, 
        sum(value / POWER(10,0)) as value
    from double_entry_book
    group by address, date
),
daily_balances_with_gaps as (
    select 
        address, 
        date,
        sum(value) over (partition by address order by date) as balance,
        lead(date, 1, current_date()) over (partition by address order by date) as next_date
        from double_entry_book_by_date
),
calendar as (
    select date from unnest(generate_date_array('2015-07-30', current_date())) as date
),
daily_balances as (
    select address, calendar.date, balance
    from daily_balances_with_gaps
    join calendar on daily_balances_with_gaps.date <= calendar.date and calendar.date < daily_balances_with_gaps.next_date
),
 supply as (
    select
        date,
        sum(balance) as daily_supply
    from daily_balances
    group by date
),
ranked_daily_balances as (
    select 
        daily_balances.date,
        balance,
        row_number() over (partition by daily_balances.date order by balance desc) as rank
    from daily_balances
    join supply on daily_balances.date = supply.date
    where safe_divide(balance, daily_supply) >= 0.0001
    ORDER BY safe_divide(balance, daily_supply) DESC
), 
gini_daily as (
   select
    date,
    -- (1 − 2B) https://en.wikipedia.org/wiki/Gini_coefficient
    1 - 2 * sum((balance * (rank - 1) + balance / 2)) / count(*) / sum(balance) as gini
  from ranked_daily_balances
  group by date
)
select date,
    gini,
    avg(gini) over (order by date asc rows 7 preceding) as gini_sma7,
    avg(gini) over (order by date asc rows 30 preceding) as gini_sma30
from gini_daily
order by date asc
"""

query_job = client.query(query)
iterator = query_job.result()
rows = list(iterator)
# Transform the rows into a nice pandas dataframe
daily_balance_gini = pd.DataFrame(data=[list(x.values()) for x in rows], columns=list(rows[0].keys()))
daily_balance_gini.head(10)
traces = []
x = daily_balance_gini['date']
gini_list = ['gini', 'gini_sma7', 'gini_sma30']
for gini in gini_list:
    y = daily_balance_gini[gini]
    trace = dict(
        x=x,
        y=y,
        hoverinfo=f'{gini}',
        mode='lines'
    )
    traces.append(trace)
fig = dict(data=traces)

iplot(fig, validate=False)
"""
# Daily Hashrate

Hashrate is a measure of difficulty over block time. We can measure this by getting the delta time of each block timestamp from the previous block timestamp.

We can average it out by day. That is, the query can average out all difficulty and delta times per day and divide them by one another. We can further divide by 1 billion to get the GH/s.

We will use the following query I wrote for the Daily Hashrate.

```
#standardSQL
-- MIT License
-- Copyright (c) 2019 Yaz Khoury, yaz.khoury@gmail.com

WITH block_rows AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY timestamp) AS rn
  FROM `bigquery-public-data.crypto_ethereum_classic.blocks`
),
delta_time AS (
  SELECT
  mp.timestamp AS block_time,
  mp.difficulty AS difficulty,
  TIMESTAMP_DIFF(mp.timestamp, mc.timestamp, SECOND) AS delta_block_time
  FROM block_rows mc
  JOIN block_rows mp
  ON mc.rn = mp.rn - 1
),
hashrate_book AS (
  SELECT TIMESTAMP_TRUNC(block_time, DAY) AS block_day,
  AVG(delta_block_time) as daily_avg_block_time,
  AVG(difficulty) as daily_avg_difficulty
  FROM delta_time
  GROUP BY TIMESTAMP_TRUNC(block_time, DAY)
)
SELECT block_day,
(daily_avg_difficulty/daily_avg_block_time)/1000000000 as hashrate
FROM hashrate_book
ORDER BY block_day ASC
```
"""
query = """
#standardSQL
-- MIT License
-- Copyright (c) 2019 Yaz Khoury, yaz.khoury@gmail.com

WITH block_rows AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY timestamp) AS rn
  FROM `bigquery-public-data.crypto_ethereum_classic.blocks`
),
delta_time AS (
  SELECT
  mp.timestamp AS block_time,
  mp.difficulty AS difficulty,
  TIMESTAMP_DIFF(mp.timestamp, mc.timestamp, SECOND) AS delta_block_time
  FROM block_rows mc
  JOIN block_rows mp
  ON mc.rn = mp.rn - 1
),
hashrate_book AS (
  SELECT TIMESTAMP_TRUNC(block_time, DAY) AS block_day,
  AVG(delta_block_time) as daily_avg_block_time,
  AVG(difficulty) as daily_avg_difficulty
  FROM delta_time
  GROUP BY TIMESTAMP_TRUNC(block_time, DAY)
)
SELECT block_day,
(daily_avg_difficulty/daily_avg_block_time)/1000000000 as hashrate
FROM hashrate_book
ORDER BY block_day ASC
"""

query_job = client.query(query)
iterator = query_job.result()
rows = list(iterator)
# Transform the rows into a nice pandas dataframe
daily_hashrate = pd.DataFrame(data=[list(x.values()) for x in rows], columns=list(rows[0].keys()))
daily_hashrate.head(10)
trace = go.Scatter(
    x=daily_hashrate['block_day'],
    y=daily_hashrate['hashrate'],
    mode='lines'
)
data = [trace]
iplot(data)