binhachonDAI on the Market (May 13) - DAI in different category
Updated 2022-05-16
99
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
›
⌄
with days_per_month as (
select
date_trunc('month', balance_date) as month_time,
max(balance_date) as balance_date
from ethereum.erc20_balances
group by 1
),
DAI_in_pools as (
select
balance_date,
case
when user_address in ('0x075e72a5edf65f0a5f44699c7654c1a76941ddc8', '0xa10c7ce4b876998858b1a9e12b10092229539400', '0x5a16552f59ea34e44ec81e58b3817833e9fd5436') then 'defi' -- PulseX, Arbitrum, cVault
when user_address in ('0x5777d92f208679db4b9778590fa3cab3ac9e2168', '0x5777d92f208679db4b9778590fa3cab3ac9e2168') then 'dex' -- uniswap v3
when user_address in ('0xfb76e9be55758d0042e003c1e46e186360f0627e') then 'dapp' --Aavegotchi: Treasury
else coalesce(label_type, 'Others') end
as corrected_label_type,
sum(balance) as total_balance
from ethereum.erc20_balances
where balance_date in (select balance_date from days_per_month)
and contract_address = '0x6b175474e89094c44da98b954eedeac495271d0f'
group by balance_date, corrected_label_type
)
select
*,
100 * ratio_to_report(total_balance) over (partition by balance_date) as percentile
from DAI_in_pools
where corrected_label_type not in ('operator', 'chadmin')
order by balance_date, percentile
Run a query to Download Data