SELECT

'2024-09-25'::DATE AS launch_date,

CURRENT_DATE AS today,

DATEADD(DAY, -DATEDIFF(DAY, '2024-09-25', CURRENT_DATE), '2024-09-25') AS pre_launch_start

, daily_activity AS (

SELECT

a.block_timestamp::DATE AS day,

COUNT(DISTINCT b.contract_address) AS total_CA,

COUNT(DISTINCT a.tx_hash) AS txns,

COUNT(DISTINCT a.from_address) AS users,

CASE

WHEN a.block_timestamp::DATE >= '2024-09-25' THEN 'After Launch'

ELSE 'Before Launch'

END AS period_type

FROM kaia.core.fact_transactions a

LEFT JOIN kaia.core.fact_event_logs b ON a.tx_hash = b.tx_hash

WHERE a.tx_succeeded = 'TRUE'

AND a.block_timestamp::DATE BETWEEN (SELECT pre_launch_start FROM date_ranges) AND (SELECT today FROM date_ranges)

GROUP BY 1, 5

)

SELECT

d.day,

d.period_type,

d.total_CA,

d.txns,

d.users

FROM daily_activity d