WITH t0 AS (

SELECT DATE_TRUNC('month', block_timestamp) AS month

, instruction:parsed:info:programAccount::string AS programAccount

, SUM(CASE WHEN event_type = 'deployWithMaxDataLen' THEN 1 ELSE 0 END) AS n_deploys

, COUNT(DISTINCT CASE WHEN event_type = 'deployWithMaxDataLen' THEN instruction:parsed:info:programAccount::string ELSE NULL END) AS n_unique_programs_deployed

, SUM(CASE WHEN event_type = 'upgrade' THEN 1 ELSE 0 END) AS n_upgrades

, COUNT(DISTINCT CASE WHEN event_type = 'upgrade' THEN instruction:parsed:info:programAccount::string ELSE NULL END) AS n_unique_programs_upgraded

FROM solana.core.fact_events e

WHERE e.block_timestamp >= '2020-01-01'

AND e.program_id = 'BPFLoaderUpgradeab1e11111111111111111111111'

AND e.EVENT_TYPE IN ('deployWithMaxDataLen','upgrade')

GROUP BY 1, 2

), t1 AS (

SELECT *

, ROW_NUMBER() OVER (PARTITION BY programAccount ORDER BY month) AS rn

FROM t0

), t2 AS (

SELECT month

, SUM(CASE WHEN rn=1 THEN 1 ELSE 0 END) AS n_new_programs_deployed

, SUM(n_deploys) AS n_deploys

, SUM(n_upgrades) AS n_upgrades

, SUM(n_unique_programs_deployed) AS n_unique_programs_deployed

, SUM(n_unique_programs_upgraded) AS n_unique_programs_upgraded

FROM t1

GROUP BY 1

SELECT *

, n_deploys + n_upgrades AS n_deploys_and_upgrades

, SUM(n_new_programs_deployed) OVER (ORDER BY month) AS cumu_new_programs_deployed

FROM t2

WHERE month < DATE_TRUNC('month', CURRENT_DATE)