allenday · January 31, 2019 10:31
diff --git a/gini-balance-bitcoin.sql b/gini-balance-bitcoin.sql
 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
	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