SELECT to_address AS address, block_timestamp

FROM bsc.core.fact_transactions

WHERE block_timestamp BETWEEN '2024-01-01' AND '2024-12-31'

UNION

SELECT from_address AS address, block_timestamp

FROM avalanche.core.fact_transactions

WHERE block_timestamp BETWEEN '2024-01-01' AND '2024-12-31'

),

daily_active_users AS (

SELECT

DATE_TRUNC('day', block_timestamp) AS transaction_date,

COUNT(DISTINCT address) AS daily_active_users,

CASE

WHEN DATE_TRUNC('day', block_timestamp) >= '2024-01-01' AND DATE_TRUNC('day', block_timestamp) < '2024-04-01' THEN 'Q1-2024'

WHEN DATE_TRUNC('day', block_timestamp) >= '2024-04-01' AND DATE_TRUNC('day', block_timestamp) < '2024-07-01' THEN 'Q2-2024'

WHEN DATE_TRUNC('day', block_timestamp) >= '2024-07-01' AND DATE_TRUNC('day', block_timestamp) < '2024-10-01' THEN 'Q3-2024'

WHEN DATE_TRUNC('day', block_timestamp) >= '2024-10-01' AND DATE_TRUNC('day', block_timestamp) < '2025-01-01' THEN 'Q4-2024'

END AS quarter

FROM combined_addresses

GROUP BY

DATE_TRUNC('day', block_timestamp)

transaction_date,

SUM(daily_active_users) OVER (PARTITION BY quarter ORDER BY transaction_date) AS cumulative_active_users,

quarter

FROM daily_active_users

ORDER BY transaction_date;