miner_total_transaction_daily_gini.sql

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


WITH miner_transaction_book AS (
  SELECT miner,
    DATE(timestamp) AS date,
    SUM(transaction_count) AS total_mined_transactions
  FROM `bigquery-public-data.crypto_ethereum_classic.blocks`
  GROUP BY miner, date
  HAVING SUM(transaction_count) > 0
),
miner_transaction_book_by_date AS (
 SELECT date,
        miner AS address,
        SUM(total_mined_transactions/ POWER(10,0)) AS value
  FROM miner_transaction_book
  GROUP BY miner, date
),
daily_transactions_with_gaps AS (
  SELECT
    address,
    date,
    SUM(value) OVER (PARTITION BY ADDRESS ORDER BY date) AS block_transactions,
    LEAD(date, 1, CURRENT_DATE()) OVER (PARTITION BY ADDRESS ORDER BY date) AS next_date
  FROM miner_transaction_book_by_date
),
calendar AS (
  SELECT date
  FROM UNNEST(GENERATE_DATE_ARRAY('2015-07-30', CURRENT_DATE())) AS date
),
daily_transactions AS (
  SELECT address,
    calendar.date,
    block_transactions
  FROM daily_transactions_with_gaps
  JOIN calendar ON daily_transactions_with_gaps.date <= calendar.date
  AND calendar.date < daily_transactions_with_gaps.next_date
),
tx_supply AS (
  SELECT date,
    SUM(block_transactions) AS total_transactions
  FROM daily_transactions
  GROUP BY date
),
ranked_daily_transactions AS (
  SELECT daily_transactions.date AS date,
    block_transactions,
    ROW_NUMBER() OVER (PARTITION BY daily_transactions.date ORDER BY block_transactions DESC) AS rank
  FROM daily_transactions
  JOIN tx_supply ON daily_transactions.date = tx_supply.date
  WHERE SAFE_DIVIDE(block_transactions, total_transactions) >= 0.001
  ORDER BY block_transactions DESC
),
daily_gini AS (
  SELECT date,
    -- (1 − 2B) https://en.wikipedia.org/wiki/Gini_coefficient
    1 - 2 * SUM((block_transactions * (rank - 1) + block_transactions / 2)) / COUNT(*) / SUM(block_transactions) AS gini
  FROM ranked_daily_transactions
  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