SELECT

DATE_TRUNC('month', deployment_block_timestamp) as month,

edition,

COUNT(DISTINCT program_id) as deployed_programs

FROM ALEO.core.dim_programs

WHERE deployment_block_timestamp >= '2024-01-01' -- Since mainnet launch

GROUP BY 1, 2

SELECT

month,

edition,

deployed_programs as "New Programs",

SUM(deployed_programs) OVER (

PARTITION BY edition

ORDER BY month

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

) as "Total Programs"

FROM deployment_activity

ORDER BY month DESC, edition;