adriaparcerisasUniswap discrete LVR alltime
Updated 2024-09-09
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
›
⌄
SELECT
DATE_TRUNC('week', BLOCK_TIMESTAMP) AS day,
--TX_HASH,
POOL_ADDRESS,
TOKEN0_SYMBOL,
TOKEN1_SYMBOL,
SUM(abs(AMOUNT1_ADJUSTED)) AS total_asset_quantity,
AVG(PRICE_0_1) AS avg_amm_price,
AVG(TOKEN1_PRICE)/AVG(TOKEN0_PRICE) AS avg_market_price,
total_asset_quantity * (avg_market_price - avg_amm_price) AS daily_LVR_discrete,
avg(daily_LVR_discrete) over (order by day) as avg_LVR_discrete,
sum(daily_LVR_discrete) over (order by day) as cum_LVR_discrete
FROM ethereum.uniswapv3.ez_swaps
WHERE POOL_NAME = '{{pool_name}}'
--AND BLOCK_TIMESTAMP BETWEEN '{{start_date}}' AND '{{end_date}}'
GROUP BY day, POOL_ADDRESS, TOKEN0_SYMBOL, TOKEN1_SYMBOL
having avg_amm_price<10000
ORDER BY day desc
QueryRunArchived: QueryRun has been archived