Distribution of Users By Transaction

Eman-RazDistribution of Users By Transaction

Updated 2025-02-04

-- forked: https://flipsidecrypto.xyz/studio/queries/6555557c-a150-485c-95fd-538bebd65980

with classification as (with tab1 as (select from_address as user, count(distinct tx_hash) as txns_count, case

when count(distinct tx_hash)=1 then 'n=1 Txn'

when count(distinct tx_hash)>1 and count(distinct tx_hash)<=5 then '1<n<=5 Txns'

when count(distinct tx_hash)>5 and count(distinct tx_hash)<=10 then '5<n<=10 Txns'

when count(distinct tx_hash)>10 and count(distinct tx_hash)<=20 then '10<n<=20 Txns'

when count(distinct tx_hash)>20 and count(distinct tx_hash)<=50 then '20<n<=50 Txns'

when count(distinct tx_hash)>50 and count(distinct tx_hash)<=100 then '50<n<=100 Txns'

when count(distinct tx_hash)>100 then 'n>100 Txns'

end as "Number of Txns"

from aurora.core.fact_transactions

where status='SUCCESS'

and block_timestamp::date>='{{Start_Date}}' and block_timestamp::date<='{{End_Date}}'

group by 1)

select "Number of Txns", count(distinct user) as "Number of Users"

from tab1

group by 1),

total_users as (select count(distinct from_address) as "Total Users"

from aurora.core.fact_transactions

where status='SUCCESS'

and block_timestamp::date>='{{Start_Date}}' and block_timestamp::date<='{{End_Date}}')

select "Number of Txns", "Number of Users", round((("Number of Users"/"Total Users")*100),3) || '%' || '' as "Percentage of Users"

from classification, total_users

order by 2 desc

Last run: 3 months ago

222B