YinkaTop 10 Pools by 24hr Volume copy
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
›
⌄
-- forked from metcalfe / Top 10 Pools by 24hr Volume @ https://flipsidecrypto.xyz/metcalfe/q/Kn326bPiIUF5/top-10-pools-by-24hr-volume
with daily_volume as(select
pool_name,
.5*(sum(abs(amount0_usd))+sum(abs(amount1_usd))) as "24hr_volume"
from uniswapv3.swaps
where block_timestamp >= DATEADD(hour, -24, CURRENT_TIMESTAMP())
group by 1),
weekly_volume as(select
pool_name,
.5*(sum(abs(amount0_usd))+sum(abs(amount1_usd))) as "7day_volume"
from uniswapv3.swaps
where block_timestamp >= DATEADD(day, -7, CURRENT_TIMESTAMP())
group by 1),
pool_stats as (select
distinct pool_name,
pool_address,
token0_address,
token1_address,
last_value(token0_balance_usd+token1_balance_usd) over (partition by pool_name,pool_address order by block_timestamp) as tvl,
last_value(tick) over (partition by pool_name,pool_address order by block_timestamp) as tick,
last_value(price_0_1) over (partition by pool_name,pool_address order by block_timestamp) as price_0_1,
last_value(price_1_0) over (partition by pool_name,pool_address order by block_timestamp) as price_1_0
from uniswapv3.pool_stats),
penult_pool_stats as (select
pool_stats.pool_name,
pool_stats.tick,
pool_stats.price_0_1,
pool_stats.price_1_0,
"24hr_volume",
"7day_volume",
tvl,
pool_address,