SELECT

DATE_TRUNC('day', block_timestamp) as date,

from_address as address,

COUNT(*) as daily_tx_count

FROM swell.core.fact_transactions where date < current_date

GROUP BY 1, 2

),

categorized_addresses AS (

SELECT

date,

CASE

WHEN daily_tx_count = 1 THEN 'Single TX'

WHEN daily_tx_count BETWEEN 2 AND 5 THEN '2-5 TXs'

WHEN daily_tx_count BETWEEN 6 AND 10 THEN '6-10 TXs'

WHEN daily_tx_count BETWEEN 11 AND 20 THEN '11-20 TXs'

ELSE '20+ TXs'

END as tx_category,

COUNT(DISTINCT address) as unique_addresses

FROM daily_tx_counts

GROUP BY 1, 2

SELECT

date,

tx_category,

unique_addresses,

SUM(unique_addresses) OVER (PARTITION BY date) as total_daily_active_addresses,

ROUND(unique_addresses * 100.0 / SUM(unique_addresses) OVER (PARTITION BY date), 2) as percentage_of_daily_total

FROM categorized_addresses

ORDER BY date DESC,

CASE tx_category

WHEN 'Single TX' THEN 1

WHEN '2-5 TXs' THEN 2

WHEN '6-10 TXs' THEN 3

WHEN '11-20 TXs' THEN 4