theericstoneUniswap V3 Moneymakers Bags
Updated 2023-03-10
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
33
34
35
36
›
⌄
with winners as (
select liquidity_provider as address,
sum(amount0_usd) as amount0_earned,
sum(amount1_usd) as amount1_earned,
coalesce(amount0_earned,0) + coalesce(amount1_earned,0) as amount_earned
from
uniswapv3.position_collected_fees
where block_timestamp > current_date - 14
group by 1
order by 4 desc
),
rankwins as (
select winners.*,
rank() over (order by amount_earned desc) as rank
from winners
)
select
contract_address,
case when contract_address = '0x6b3595068778dd592e39a122f4f5a5cf09c90fe2' then 'SUSHI'
when contract_address = '0xd533a949740bb3306d119cc777fa900ba034cd52' then 'CRV'
when contract_address = '0x3845badade8e6dff049820680d1f14bd3903a5d0' then 'SAND'
when contract_address = '0x57ab1ec28d129707052df4df418d58a2d46d5f51' then 'sUSD'
when contract_address = '0xa0246c9032bc3a600820415ae600c6388619a14d' then 'FARM'
when contract_address = '0x8798249c2e607446efb7ad49ec89dd1865ff4272' then 'xSUSHI'
when contract_address = '0x15d4c048f83bd7e37d49ea4c83a07267ec4203da' then 'GALA'
when contract_address = '0x429881672b9ae42b8eba0e26cd9c73711b891ca5' then 'PICKLE'
when contract_address = '0x990f341946a3fdb507ae7e52d17851b87168017c' then 'STRONG'
when contract_address = '0x95ad61b0a150d79219dcf64e1e6cc01f0b64c4ce' then 'SHIB'
else symbol end as symbol,
count(distinct(user_address)) as n_hodlers,
sum(coalesce(amount_usd,0)) as amount_held
from ethereum.erc20_balances
where balance_date = (select max(balance_date) from ethereum.erc20_balances where balance_date > current_date - 3)
and user_address in (select address from rankwins where rank < 101)
Run a query to Download Data