Eman-RazTotal Swap Volume By Token Pairs
Updated 2024-07-12
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 table1 as (with tab1 as (select date_trunc('hour',block_timestamp) as "Date", sum(token_in_amount) as "Swap Volume ($FLOW)",
split_part(token_in_contract,'.',3) || '➡' || split_part(token_out_contract,'.',3) as "Token Pair"
from flow.defi.ez_swaps
where block_timestamp::date between '{{Start_Date}}' and '{{End_Date}}'
and token_in_contract='A.1654653399040a61.FlowToken'
group by 1,3),
tab2 as (select hour, avg(price) as price
from flow.price.ez_prices_hourly
where symbol='FLOW' and hour::date between '{{Start_Date}}' and '{{End_Date}}'
group by 1)
select "Token Pair", sum("Swap Volume ($FLOW)") as "Swap Volume ($FLOW)",
sum("Swap Volume ($FLOW)"*price) as "Swap Volume ($USD)"
from tab1 left join tab2 on tab1."Date"=tab2.hour
group by 1
order by 2 desc),
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
table2 as (with tab1 as (select date_trunc('hour',block_timestamp) as "Date", sum(token_out_amount) as "Swap Volume ($FLOW)",
split_part(token_in_contract,'.',3) || '➡' || split_part(token_out_contract,'.',3) as "Token Pair"
from flow.defi.ez_swaps
where block_timestamp::date between '{{Start_Date}}' and '{{End_Date}}'
and token_out_contract='A.1654653399040a61.FlowToken'
group by 1,3),
tab2 as (select hour, avg(price) as price
from flow.price.ez_prices_hourly
where symbol='FLOW' and hour::date between '{{Start_Date}}' and '{{End_Date}}'
group by 1)
select "Token Pair", sum("Swap Volume ($FLOW)") as "Swap Volume ($FLOW)",
sum("Swap Volume ($FLOW)"*price) as "Swap Volume ($USD)"
from tab1 left join tab2 on tab1."Date"=tab2.hour
QueryRunArchived: QueryRun has been archived