hyoeisemandap 5
Updated 2022-10-19Copy 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
›
⌄
with p1 as (select date_trunc( 'week',block_timestamp) as weekly1, tx_from as daily_active_users,
count (distinct (date_trunc('day',block_timestamp))) as count_active_day
from osmosis.core.fact_transactions
where tx_status ilike 'SUCCEEDED' and block_timestamp <= current_date - 1
group by 1,2
having count_active_day >=4),
p2 as (select 'swap' as type, date_trunc ('week',block_timestamp) as week, count (distinct tx_id) as number_of_transactions,
count (distinct trader) as number_of_active_users
from osmosis.core.fact_swaps
where tx_status ilike 'SUCCEEDED' and block_timestamp <= current_date - 1
and trader in (select daily_active_users from p1) group by 1,2),
p3 as (select 'LP' as type, date_trunc ('week',block_timestamp) as week, count (distinct tx_id) as number_of_transactions,
count (distinct liquidity_provider_address) as number_of_active_users
from osmosis.core.fact_liquidity_provider_actions
where tx_status ilike 'SUCCEEDED' and block_timestamp <= current_date - 1
and liquidity_provider_address in (select daily_active_users from p1) group by 1,2),
p4 as (select 'transfer_in' as type, date_trunc ('week',block_timestamp) as week, count (distinct tx_id) as number_of_transactions,
count (distinct receiver) as number_of_active_users
from osmosis.core.fact_transfers
where tx_status ilike 'SUCCEEDED' and transfer_type ilike 'IBC_TRANSFER_IN' and block_timestamp <= current_date - 1
and receiver in (select daily_active_users from p1) group by 1,2),
p5 as (select 'transfer_out' as type, date_trunc ('week',block_timestamp) as week, count (distinct tx_id) as number_of_transactions,
count (distinct sender) as number_of_active_users
from osmosis.core.fact_transfers
where tx_status ilike 'SUCCEEDED' and transfer_type ilike 'IBC_TRANSFER_OUT' and block_timestamp <= current_date - 1
and sender in (select daily_active_users from p1) group by 1,2)
(select * from p2) union all (select * from p3) union all (select * from p4) union all (select * from p5)
Run a query to Download Data