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 -- Balanced pre-launch start date

, weekly_activity AS (

SELECT

DATE_TRUNC('day', block_timestamp)::DATE AS Date,

COUNT(DISTINCT from_address) AS active_users,

COUNT(DISTINCT tx_hash) AS transactions,

SUM(value) AS txn_volume,

SUM(TX_FEE) AS total_tx_fee,

CASE

WHEN DATE_TRUNC('day', block_timestamp)::DATE >= '2024-09-25' THEN 'After Launch'

ELSE 'Before Launch'

END AS period_type

FROM kaia.core.fact_transactions, date_range

WHERE tx_succeeded = 'TRUE'

AND block_timestamp::DATE BETWEEN (SELECT pre_launch_start FROM date_range) AND (SELECT today FROM date_range)

GROUP BY 1, 6

Date,

period_type,

active_users,

transactions,

txn_volume,

total_tx_fee

FROM weekly_activity