SELECT

from_address,

COUNT(DISTINCT tx_hash) AS tx_count

FROM

mantle.core.fact_transactions

WHERE

block_timestamp >= CURRENT_DATE - 30

AND

from_address != '0xdeaddeaddeaddeaddeaddeaddeaddeaddead0001'

GROUP BY

from_address

),

buckets AS (

SELECT

CASE

WHEN tx_count = 1 THEN '1'

WHEN tx_count BETWEEN 2 AND 10 THEN '2–10'

WHEN tx_count BETWEEN 11 AND 100 THEN '11–100'

WHEN tx_count BETWEEN 101 AND 1000 THEN '101–1K'

WHEN tx_count BETWEEN 1001 AND 10000 THEN '1K–10K'

WHEN tx_count BETWEEN 10001 AND 100000 THEN '10K–100K'

WHEN tx_count BETWEEN 100001 AND 1000000 THEN '100K–1M'

ELSE '1M+'

END AS tx_bucket,

COUNT(*) AS user_count

FROM

user_tx_counts

GROUP BY

tx_bucket

tx_bucket,

user_count

buckets