CryptoIcicle32. - Whales Comin’ and Goin’ - Leaving
Updated 2022-01-30Copy Reference Fork
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
›
⌄
-- Define a whale.
-- Show the number of whales leaving liquidity pools on Sushiswap each day over the past 2 months.
-- Do the same for the number of whales entering liquidity pools.
-- Payout 12.89 SUSHI
-- Grand Prize 38.67 SUSHI
-- Level Beginner
with
sushi_pools as (
select
distinct(p.pool_name) as pool_name,
p.pool_address,
sum(IFF(amount_usd > pow(10,9), amount_usd/pow(10,18), amount_usd)) as balance
from ethereum.erc20_balances u
join ethereum.dex_liquidity_pools p
on u.user_address = p.pool_address
where
platform = 'sushiswap'
-- https://discord.com/channels/784442203187314689/865201778727452683/937187858488385536
and balance_date = CURRENT_DATE - 6 -- The Recent data doesn't have balance in USD
and amount_usd > 0
group by pool_name, pool_address
order by balance desc
limit 25
),
liquidity_txns_usd as (
select
pool_name,
t.pool_address,
from_address,
sum(IFF(amount_usd > pow(10,9), amount_usd/pow(10,18), amount_usd)) as liquidity_usd,
rank() over (partition by pool_name order by liquidity_usd desc ) as rank
from ethereum.udm_events e
join sushi_pools t on t.pool_address = to_address
Run a query to Download Data