SalehUntitled Query
Updated 2022-11-20
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
›
⌄
with pricet as (
select
timestamp::Date as day
,token
,symbol
,token_contract
,avg (price_usd) as USDPrice
from flow.core.fact_prices
where token != 'Blocto'
group by 1,2,3,4
)
select
symbol,
count (distinct tx_id) as Swaps_Count,
count (distinct trader) as Swappers_Count,
sum (case when token_in_contract = token_contract then token_in_amount when token_out_contract = token_contract then token_out_amount end) as Total_Token_Volume,
sum(token_in_amount+token_out_amount)
-- sum (case when token_in_contract = token_contract then token_in_amount*usdprice when token_out_contract = token_contract then token_out_amount*usdprice end) as Total_USD_Volume,
-- avg (case when token_in_contract = token_contract then token_in_amount when token_out_contract = token_contract then token_out_amount end) as Average_Token_Volume,
-- avg (case when token_in_contract = token_contract then token_in_amount*usdprice when token_out_contract = token_contract then token_out_amount*usdprice end) as Average_USD_Volume,
-- median (case when token_in_contract = token_contract then token_in_amount when token_out_contract = token_contract then token_out_amount end) as Median_Token_Volume,
-- median (case when token_in_contract = token_contract then token_in_amount*usdprice when token_out_contract = token_contract then token_out_amount*usdprice end) as Median_USD_Volume,
-- min (case when token_in_contract = token_contract then token_in_amount when token_out_contract = token_contract then token_out_amount end) as Minimum_Token_Volume,
-- min (case when token_in_contract = token_contract then token_in_amount*usdprice when token_out_contract = token_contract then token_out_amount*usdprice end) as Minimum_USD_Volume,
-- max (case when token_in_contract = token_contract then token_in_amount when token_out_contract = token_contract then token_out_amount end) as Maximum_Token_Volume,
-- max (case when token_in_contract = token_contract then token_in_amount*usdprice when token_out_contract = token_contract then token_out_amount*usdprice end) as Maximum_USD_Volume
from flow.core.ez_swaps t1 join pricet t2 on (t1.token_in_contract = t2.token_contract or t1.token_out_contract = t2.token_contract) and t1.block_timestamp::Date = t2.day
group by 1
order by 2 desc
Run a query to Download Data