ArioUser Behavior (redux) - avg swap Optimism
Updated 2022-10-25
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 Top_10 as (
select Top 10 Pool_name,
sum(AMOUNT_IN_USD) as Swap_Volume
from optimism.sushi.ez_swaps
where 1=1
and block_timestamp >= CURRENT_DATE-30
and AMOUNT_IN_USD is not null
and POOL_NAME is not null
group by 1
order by 2 desc
),
daily as (select
date_trunc('day',block_timestamp) as date,
POOL_NAME,
avg(AMOUNT_IN_USD) as "Avg-Swap Volume($USD)"
from optimism.sushi.ez_swaps
where 1=1
and block_timestamp >= CURRENT_DATE-30
and AMOUNT_IN_USD is not null
and POOL_NAME is not null
and Pool_name in (select Pool_name from Top_10)
group by 1,2
),
avg as (
SELECT
date_trunc('day',block_timestamp) as date,
avg(AMOUNT_IN_USD) as "Total-Avg-Swap-Volume($USD)"
from optimism.sushi.ez_swaps
where 1=1
and block_timestamp >= CURRENT_DATE-30
and AMOUNT_IN_USD is not null
and POOL_NAME is not null
group by 1
)
select
d.date,
Run a query to Download Data