-
Notifications
You must be signed in to change notification settings - Fork 6
/
gini.sql
79 lines (79 loc) · 2.94 KB
/
gini.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
with double_entry_book as (
select to_address as address, value as value, block_timestamp
-- debits
from `bigquery-public-data.crypto_ethereum_classic.traces`
where to_address is not null
and status = 1
and (call_type not in ('delegatecall', 'callcode', 'staticcall') or call_type is null)
union all
-- credits
select from_address as address, -value as value, block_timestamp
from `bigquery-public-data.crypto_ethereum_classic.traces`
where from_address is not null
and status = 1
and (call_type not in ('delegatecall', 'callcode', 'staticcall') or call_type is null)
union all
-- transaction fees debits
select miner as address, sum(cast(receipt_gas_used as numeric) * cast(gas_price as numeric)) as value, timestamp as block_timestamp
from `bigquery-public-data.crypto_ethereum_classic.transactions` as transactions
join `bigquery-public-data.crypto_ethereum_classic.blocks` as blocks on blocks.number = transactions.block_number and blocks.timestamp = transactions.block_timestamp
group by blocks.miner, block_timestamp
union all
-- transaction fees credits
select from_address as address, -(cast(receipt_gas_used as numeric) * cast(gas_price as numeric)) as value, block_timestamp
from `bigquery-public-data.crypto_ethereum_classic.transactions`
),
double_entry_book_by_date as (
select
date(block_timestamp) as date,
address,
sum(value * 0.00000001) 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 > 1
),
address_counts as (
select
date,
count(*) as address_count
from
daily_balances
group by date
),
daily_balances_sampled as (
select address, daily_balances.date, balance
from daily_balances
join address_counts on daily_balances.date = address_counts.date
where mod(abs(farm_fingerprint(address)), 100000000)/100000000 <= safe_divide(10000, address_count)
)
,ranked_daily_balances as (
select
date,
balance,
row_number() over (partition by date order by balance desc) as rank
from daily_balances_sampled
)
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
having sum(balance) > 0
order by date asc;