Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Calculate Gini coefficient for Bitcoin balances.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.