Hessishsim strk arb - tx level
Updated 2023-12-11
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
›
⌄
with accs as (SELECT DISTINCT CONCAT('0x', LTRIM(CONTRACT, '0x')) as addr, CONTRACT as addr0
from external.tokenflow_starknet.decoded_traces
where
CHAIN_ID = 'mainnet'
and
FUNCTION = 'constructor'
),
tb1 as (SELECT DISTINCT CONTRACT, count(DISTINCT tx_hash) as txs
from external.tokenflow_starknet.decoded_transactions
where
CHAIN_ID = 'mainnet'
and CONTRACT in (SELECT DISTINCT addr0 from accs )
group by 1)
select
case when txs < 4 then 'A.less than 4 txs'
when txs>=4 and txs<10 then 'B.Between 4~10 txs'
when txs>=10 and txs<25 then 'C.Between 10~25 txs'
when txs>=25 and txs<100 then 'D.Between 25~100 txs'
when txs>=100 then 'E.+100 txs'
end as tx_level,
count(distinct CONTRACT) as users
from tb1
group by 1
Run a query to Download Data