bigquery-dogecoin-gini-incomes-limited.sql

with 
d0 as (
    select
        array_to_string(x.addresses,',') as address,
        date(block_timestamp) as date,
        value
        from `bigquery-public-data.crypto_dogecoin.transactions` join unnest(outputs) as x
)
,daily_incomes as (
    select
        address,
        date,
        sum(value) as income
        from d0
        GROUP BY address, date
)
,limited_daily_incomes as (
    select 
        daily_incomes.date,
        array_agg(struct(income) ORDER BY income desc LIMIT 10000) as incomes
    from daily_incomes
    group by date
)
,ranked_daily_incomes as (
    select 
        date,
        income,
        row_number() over (partition by date order by income desc) as rank
    from limited_daily_incomes cross join unnest(limited_daily_incomes.incomes) as incomes
)
select 
    date, 
    -- (1 − 2B) https://en.wikipedia.org/wiki/Gini_coefficient
    1 - 2 * sum((income * (rank - 1) + income / 2)) / count(*) / sum(income) as gini
from ranked_daily_incomes
group by date
order by date asc