SELECT

'2024-09-25'::DATE AS launch_date, -- Explicitly cast as DATE

CURRENT_DATE AS today,

DATEADD(DAY, -DATEDIFF(DAY, '2024-09-25', CURRENT_DATE), '2024-09-25') AS pre_launch_start -- Balanced pre-launch start date

, daily_CA AS (

SELECT

block_timestamp::DATE AS day,

COUNT(DISTINCT contract_address) AS total_CA,

CASE

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

ELSE 'Before Launch'

END AS period_type

FROM kaia.core.fact_event_logs, date_ranges

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

GROUP BY 1, 3

, avg_CA_per_day AS (

SELECT

period_type,

COUNT(DISTINCT day) AS num_days,

SUM(total_CA) AS total_CA,

AVG(total_CA) AS avg_CA_per_day

FROM daily_CA

GROUP BY 1

SELECT

d.day,

d.period_type,

d.total_CA,

a.avg_CA_per_day

FROM daily_CA d