WITH t0 AS (

SELECT DATE_TRUNC('week', block_timestamp) AS WEEK

, 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 >= dateadd('year',-1,CURRENT_DATE)

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 WEEK) AS rn

FROM t0

), t2 AS (

SELECT WEEK

, SUM(CASE WHEN rn=1 THEN 1 ELSE 0 END) AS "Deployed Programs Count",

SUM(n_upgrades) AS "Upgraded Programs Count"

FROM t1

GROUP BY 1

SELECT *

, "Deployed Programs Count" + "Upgraded Programs Count" AS "Deploy + Upgrade Count"

, SUM("Deployed Programs Count") OVER (ORDER BY WEEK) AS "Cumulative Update and Upgrade count"

FROM t2