nitsQualified Pools Volume By Symbol
Updated 2022-07-06
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
›
⌄
with last_use as
(SELECT pool_name as pn ,max(block_timestamp) as l from uniswapv3.pool_stats
where token1_balance_usd is not NULL and token0_balance_usd is not NULL
GROUP by 1 ),
uni_total_tvl as
(SELECT sum(liquidity) as total_liquidity_uniswap from
(SELECT *, token0_balance_usd+token1_balance_usd as liquidity from
(SELECT * from uniswapv3.pool_stats
inner join last_use on l= block_timestamp and pn = pool_name ))),
pon as
(SELECT DISTINCT pn from
(SELECT *, token0_balance_usd+token1_balance_usd as liquidity from
(SELECT * from uniswapv3.pool_stats
inner join last_use on l= block_timestamp and pn = pool_name ))
where liquidity >= pow(10,5)),
symbol1 as
(SELECT date(block_timestamp) as day,token1_symbol as s, sum(abs(amount0_usd)) as total_amt
from uniswapv3.swaps
where pool_name in (SELECT * from pon ) and block_id > 15063464-100000
GROUP by 1, 2),
symbol0 as
(SELECT date(block_timestamp) as day,token0_symbol as s , sum(abs(amount0_usd)) as total_amt
from uniswapv3.swaps
where pool_name in (SELECT * from pon ) and block_id > 15063464-100000
GROUP by 1, 2)
SELECT day, s, sum(total_amt) as total_amt_
from
(SELECT * from symbol0
UNION ALL
SELECT * FROM symbol1 )
GROUP by 1 , 2
-- LIMIT 10
Run a query to Download Data