scottincryptoUni Active Pools
Updated 2021-06-13
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
›
⌄
with swap_query as (
select
date_trunc('day', block_timestamp) as date,
pool_name,
count(tx_id) as swaps
from uniswapv3.swaps
where block_timestamp > getdate() - interval'30 day'
group by 1,2
order by 3 desc
),
lp_query as (
select
date_trunc('day', block_timestamp) as date,
pool_name,
count(tx_id) as lp_actions
from uniswapv3.lp_actions
where block_timestamp > getdate() - interval'30 day'
group by 1,2
order by 3 desc
)
SELECT
s.pool_name,
ifnull(avg(s.swaps),0) as swap,
ifnull(avg(l.lp_actions),0) as lp_action,
swap + lp_action as activities
from swap_query s full outer join lp_query l on (s.date = l.date and s.pool_name = l.pool_name)
group by 1
order by activities desc
Run a query to Download Data