mlhtotal swap
Updated 2022-11-23Copy Reference Fork
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
›
⌄
with price as (select timestamp::Date as day,
token,
token_contract,
avg (price_usd) as USDPrice
from flow.core.fact_prices
where token != 'Blocto'
group by 1, 2, 3
)
select substr(token_in_contract,20) || ' - ' || substr(token_out_contract,20) as pair,
count (distinct tx_id) as swaps,
count (distinct trader) as swappers,
sum (case when token_in_contract = token_contract then token_in_amount
when token_out_contract = token_contract then token_out_amount end) as Volume,
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 USD_Volume
from flow.core.ez_swaps a join price b on (a.token_in_contract = b.token_contract or a.token_out_contract = b.token_contract)
and a.block_timestamp::Date = b.day
where token in ('USDC', 'Flow', 'Starly', 'Sportium', 'REVV', 'FUSD', 'Blocto Token', 'USDT')
group by 1
Run a query to Download Data