Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Calculate Gini coefficient for ERC-20 balances.
with
double_entry_book as (
-- debits
select to_address as address, CAST(value AS NUMERIC) as value, block_timestamp
from `bigquery-public-data.crypto_ethereum.token_transfers`
where from_address is not null and to_address is not null
and token_address = LOWER('0x408e41876cccdc0f92210600ef50372656052a38') --OMG
union all
-- credits
select from_address as address, -CAST(value AS NUMERIC) as value, block_timestamp
from `bigquery-public-data.crypto_ethereum.token_transfers`
where from_address is not null and to_address is not null
and token_address = LOWER('0x408e41876cccdc0f92210600ef50372656052a38') --OMG
)
,double_entry_book_by_date as (
select
date(block_timestamp) as date,
address,
sum(value / POWER(10,18)) 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
where balance >= 0
)
,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
)
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.