WITH double_entry_book AS ( -- debits SELECT array_to_string(inputs.addresses, ",") as address , inputs.type , -inputs.value as value , block_timestamp FROM `bigquery-public-data.crypto_bitcoin.inputs` as inputs UNION ALL -- credits SELECT array_to_string(outputs.addresses, ",") as address , outputs.type , outputs.value as value , block_timestamp FROM `bigquery-public-data.crypto_bitcoin.outputs` as outputs ) ,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('2009-01-12', 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 ) 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 order by date asc