mohammadhCosmoverse Free Play 5 (2))sell(tx category)
Updated 2022-10-13
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 A as (select
sum(FROM_AMOUNT/1e6) volume,
date_trunc('day',block_timestamp) as block_day,
count(distinct TX_ID) as tx_count,
count(distinct TRADER) as unique_user,
case
when FROM_AMOUNT/1e6 < 100 then 'less than 100'
when FROM_AMOUNT/1e6 between 100 and 1000 then '100-1000'
when FROM_AMOUNT/1e6 between 1000 and 2000 then '1000-2000'
when FROM_AMOUNT/1e6 between 2000 and 5000 then '2000-5000'
when FROM_AMOUNT/1e6 between 5000 and 10000 then '5000-10000'
when FROM_AMOUNT/1e6 between 10000 and 50000 then '10000-50000'
when FROM_AMOUNT/1e6 > 50000 then 'biger than 50000'
end as tx_category
from osmosis.core.fact_swaps
where (block_timestamp >= '2022-10-01' and block_timestamp <= '2022-10-10')
and FROM_CURRENCY ='ibc/0954E1C28EB7AF5B72D24F3BC2B47BBB2FDF91BDDFD57B74B99E133AED40972A'
and TX_STATUS ='SUCCEEDED'
and (block_day >= '2022-10-01' and block_day <= '2022-10-10')
group by block_day,tx_category)
select *,
sum(volume) over (partition by tx_category order by block_day asc) as cum_volume,
sum(unique_user) over (partition by tx_category order by block_day asc) as cum_user,
sum(tx_count) over (partition by tx_category order by block_day asc) as cum_tx
from A
Run a query to Download Data