CryptoLionTop 5 Pools liq
Updated 2023-03-27
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
›
⌄
WITH top as (
SELECT
pool_name,
sum(abs(amount0_usd)) as v_volume
FROM uniswapv3.swaps
WHERE block_timestamp >= getdate() - interval '7 days'
GROUP BY 1
HAVING v_volume IS NOT NULL
ORDER BY 2 DESC
LIMIT 5
),
liq as (
SELECT
date_trunc('week',block_timestamp) as week,
uniswapv3.lp_actions.pool_name,
SUM(CASE WHEN action = 'INCREASE_LIQUIDITY' THEN amount0_usd+amount1_usd ELSE 0 END) as add_liq,
SUM(CASE WHEN action = 'DECREASE_LIQUIDITY' THEN amount0_usd+amount1_usd ELSE 0 END) as sub_liq
FROM uniswapv3.lp_actions
INNER JOIN top on top.pool_name = uniswapv3.lp_actions.pool_name
WHERE block_timestamp >= getdate() - interval '10 weeks'
GROUP BY 1,2
ORDER BY 1 DESC
)
SELECT
week,
pool_name,
add_liq-sub_liq as liq_volume,
LAG(liq_volume) IGNORE NULLS OVER (ORDER BY week) as last_liq,
liq_volume-last_liq as change_liq
FROM liq
GROUP BY 1,2,3
ORDER BY 1 DESC