WITH t0 AS (

SELECT DATE(block_timestamp) AS date

, 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 signers[0] ELSE 0 end) as n_deployers

, 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 date) AS rn

FROM t0

), t2 AS (

SELECT date

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

, SUM(n_deploys) AS n_deploys

, SUM (n_deployers) as n_deployers

, 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 *