FatemeTheLadySCRT price and swap volume - last month
Updated 2022-10-12Copy Reference Fork
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 ev_price_tbl as (
select date(RECORDED_AT) as pdate, avg(price) as price
from osmosis.core.dim_prices
where symbol = 'SCRT' and pdate<CURRENT_DATE and pdate>CURRENT_DATE-61
group by pdate
),
f as(
select date_trunc('day', block_timestamp) as date,
case when Date>CURRENT_DATE-8 then 'last week' else 'other days' end as "Date status",
count(distinct TX_ID) as swaps,
count(distinct TRADER) as swappers,
sum(case when FROM_CURRENCY = 'ibc/0954E1C28EB7AF5B72D24F3BC2B47BBB2FDF91BDDFD57B74B99E133AED40972A' then FROM_AMOUNT/1e6 * price else TO_AMOUNT/1e6 * price end) as usd_vol,
avg(usd_vol) over(order by date asc) as avg_usd_vol,
avg(swaps) over(order by date asc) as avg_swaps_n,
sum(usd_vol) over (order by date asc) as cumu_usd_vol,
sum(swaps) over (order by date asc) as cumu_swaps,
sum(swappers) over (order by date asc) as cumu_swapers,
avg(swappers) over(order by date asc) as avg_swappers_n,
usd_vol/swappers as "Volume per user",
price
from osmosis.core.fact_swaps
join ev_price_tbl on ev_price_tbl.pdate = date(block_timestamp)
where (FROM_CURRENCY = 'ibc/0954E1C28EB7AF5B72D24F3BC2B47BBB2FDF91BDDFD57B74B99E133AED40972A' OR TO_CURRENCY = 'ibc/0954E1C28EB7AF5B72D24F3BC2B47BBB2FDF91BDDFD57B74B99E133AED40972A' )
and date<CURRENT_DATE and Date>CURRENT_DATE-30
group by date, price
)
select date
,"Date status"
, usd_vol
, swaps
, swappers
from f
order by 1 asc
Run a query to Download Data