john-adamUntitled Query
Updated 2022-11-16
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 pricet as (
select recorded_at::date as day,
symbol as symbol1,
avg (price) as USDPrice
from osmosis.core.dim_prices
group by 1,2
union ALL
select hour::date as day,
case when symbol = 'USDC' then 'USDC.axl'
when symbol = 'USDT' then 'USDT.axl'
when symbol = 'aTUSD' then 'USDC.grv'
when symbol = 'sUSD' then 'USDT.grv'
when symbol = 'iUSD' then 'USDX'
when symbol = 'WETH' then 'WETH.axl'
when symbol = 'aWETH' then 'WETH.grv'
when symbol = 'WBTC' then 'WBTC.axl'
when symbol = 'uWBTC' then 'WBTC.grv'
when symbol = 'DAI' then 'DAI.axl'
when symbol = 'aDAI' then 'DAI.grv'
when symbol = 'FRAX' then 'FRAX.axl'
else symbol end as symbol1,
avg (price) as USDPrice
from ethereum.core.fact_hourly_token_prices
group by 1,2)
select block_timestamp::date as date,
case when date < '2022-11-08' then 'Before FTX Collapse'
when date >= '2022-11-08' then 'During and After FTX Collapse' end as timespan,
count (distinct tx_id) as Swaps_Count,
count (distinct trader) as Swappers_Count,
sum (case when from_currency = 'ibc/64BA6E31FE887D66C6F8F31C7B1A80C7CA179239677B4088BB55F5EA07DBE273' then from_amount*USDPrice/pow(10,18) else from_amount*USDPrice/pow(10,from_decimal) end) as Total_Volume,
avg (case when from_currency = 'ibc/64BA6E31FE887D66C6F8F31C7B1A80C7CA179239677B4088BB55F5EA07DBE273' then from_amount*USDPrice/pow(10,18) else from_amount*USDPrice/pow(10,from_decimal) end) as Average_Volume
from osmosis.core.fact_swaps t1 join osmosis.core.dim_labels t2 on t1.from_currency = t2.address
Run a query to Download Data