Moesegm4* frst act
Updated 2023-01-03Copy 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 raw as (
select
t.*,
row_number()over(partition by receiver order by BLOCK_TIMESTAMP asc) as n
from
osmosis.core.fact_transfers t
where TRANSFER_TYPE = 'IBC_TRANSFER_IN'
)
,fin as (select
x.*,
row_number()over(partition by tx_from order by x.BLOCK_TIMESTAMP asc) as rn
from
osmosis.core.fact_transactions x, raw r
where
tx_from = receiver
and
x.BLOCK_TIMESTAMP>r.BLOCK_TIMESTAMP )
select
date_trunc(week,BLOCK_TIMESTAMP)::date as date,
case
when tx_id in (select tx_id from osmosis.core.fact_staking ) then 'Staking'
when tx_id in (select tx_id from osmosis.core.fact_swaps ) then 'Swapping'
when tx_id in (select tx_id from osmosis.core.fact_transfers ) then 'Transfer'
when tx_id in (select tx_id from osmosis.core.fact_liquidity_provider_actions ) then 'Lping'
end as act,
count( distinct tx_id) as txns ,
sum(txns)over(partition by act order by date rows between unbounded preceding and current row ) as cumulative_txns,
count( distinct tx_from) as userss ,
sum(userss)over(partition by act order by date rows between unbounded preceding and current row ) as cumulative_userss,
from fin
where
rn = 1
Run a query to Download Data