phi-deltalyticsUniLPDailyTtl
Updated 2023-06-08Copy 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
›
⌄
WITH ttl_lp_adj AS (
SELECT
dt,
SUM(CASE WHEN num_mint < num_burn THEN num_mint ELSE num_burn END) AS num_adj_price_range
FROM (
SELECT
DATE_TRUNC('day', block_timestamp) AS dt,
l.pool_address,
l.liquidity_provider,
COUNT(DISTINCT (CASE WHEN action = 'INCREASE_LIQUIDITY' THEN tx_hash ELSE NULL END)) AS num_mint,
COUNT(DISTINCT (CASE WHEN action = 'DECREASE_LIQUIDITY' THEN tx_hash ELSE NULL END)) AS num_burn
FROM
ethereum.uniswapv3.ez_lp_actions l
GROUP BY 1,2,3
)
GROUP BY dt
)
SELECT
*,
num_adj_price_range / num_lp AS avg_lp_adj
FROM (
SELECT
DATE_TRUNC('day', block_timestamp) AS dt,
COUNT(DISTINCT liquidity_provider) AS num_lp
FROM ethereum.uniswapv3.ez_lp_actions
GROUP BY dt
) a LEFT JOIN ttl_lp_adj lp ON a.dt = lp.dt
ORDER BY a.dt DESC
Run a query to Download Data