alirsethereum TX count per user
Updated 2022-07-19
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 user_TX as (select
FROM_ADDRESS as users,
count(distinct TX_HASH) over (partition by users) as Num_transaction
from ethereum.core.fact_transactions
where
BLOCK_TIMESTAMP::date>='2022-04-20' and
STATUS='SUCCESS'
)
select
case
when Num_transaction=1 then 'A-Only one transaction'
when Num_transaction>1 and Num_transaction<=10 then 'B-transaction between 1 and 10'
when Num_transaction>10 and Num_transaction<=50 then 'C- transaction between 10 and 50'
when Num_transaction>50 and Num_transaction<=100 then 'D- transaction between 50 and 100'
when Num_transaction>100 and Num_transaction<=500 then 'E-transaction between 100 and 500'
when Num_transaction>500 then 'F- Greater than 500'
end as group1,
case
when Num_transaction=1 then '1'
when Num_transaction>1 and Num_transaction<=10 then '2'
when Num_transaction>10 and Num_transaction<=50 then '3'
when Num_transaction>50 and Num_transaction<=100 then '4'
when Num_transaction>100 and Num_transaction<=500 then '5'
when Num_transaction>500 then '6'
end as rank,
count(distinct users) as TX_count
from user_TX
group by 1,2
order by 2
Run a query to Download Data