MLDZMNbbon10
Updated 2023-01-04
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 tb1 as (select
date_trunc('day',recorded_at) as day,
avg (price) as usd_price
from osmosis.core.dim_prices
where symbol='OSMO'
and recorded_at::date>='2022-03-01'
group by 1
)
select
date_trunc('week',block_timestamp) as date,
'Swap from OSMO' as swap_type,
count (distinct tx_id) as no_transfer,
count (distinct trader) as no_senders,
sum((from_amount/POW (10,from_decimal))*usd_price) as transfer_usd,
avg((from_amount/POW (10,from_decimal))*usd_price) as avg_transfer_usd,
sum(no_transfer) over (order by date) as cum_transfer,
sum(transfer_usd) over (order by date) as cum_transfer_usd,
avg(avg_transfer_usd) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as MA_7_Days
from osmosis.core.fact_swaps s left join tb1 b on s.block_timestamp::date = b.day
where FROM_CURRENCY='uosmo'
and TX_STATUS = 'SUCCEEDED'
and block_timestamp>='2022-03-12'
group by 1,2
union all
select
date_trunc('week',block_timestamp) as date,
'Swap To OSMO' as swap_type,
count (distinct tx_id) as no_transfer,
count (distinct trader) as no_senders,
sum((to_amount/POW (10,to_decimal))*usd_price) as transfer_usd,
avg((to_amount/POW (10,to_decimal))*usd_price) as avg_transfer_usd,
sum(no_transfer) over (order by date) as cum_transfer,
sum(transfer_usd) over (order by date) as cum_transfer_usd,
avg(avg_transfer_usd) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as MA_7_Days
from osmosis.core.fact_swaps s left join tb1 b on s.block_timestamp::date = b.day
Run a query to Download Data