mmdrezaDistribstion Active users by Count of transactions
Updated 2022-10-17
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
›
⌄
with tab as (
select date_trunc ('week',block_timestamp) as date,
tx_from as Users,
count (distinct tx_id) as transactions_count
from osmosis.core.fact_transactions
where tx_status = 'SUCCEEDED'
group by 1,2
having transactions_count >= 20),
tab2 as (
select
tx_from as user,
count (distinct tx_id) as tx_count
from osmosis.core.fact_transactions
where tx_status = 'SUCCEEDED'
and user in (select users from tab)
group by 1)
select
'transactions' as type,
case
when tx_count > 1 and tx_count <= 10 then 'between 1 and 10'
when tx_count > 10 and tx_count <= 20 then 'between 10 and 20'
when tx_count > 20 and tx_count <= 50 then 'between 20 and 50'
when tx_count > 50 and tx_count <= 100 then 'between 50 and 100'
when tx_count > 100 and tx_count <= 500 then 'between 100 and 500'
when tx_count > 500 and tx_count <= 1000 then 'between 500 and 1000'
when tx_count > 1000 then 'more than 100 swaps'
end count_transactions,
count(distinct user) as count_user
from tab2
group by 1,2
Run a query to Download Data