ssq.time,

new_prog as "New program",

(unique_prog - new_prog) as "Old program"

FROM (

SELECT

sq.time,

COUNT(*) AS new_prog

FROM (

SELECT

INSTRUCTIONS[0]:programId as unique_prog,

MIN(date_trunc('day', BLOCK_TIMESTAMP)) AS time

FROM solana.core.fact_transactions

GROUP BY 1

ORDER BY 1

) sq

GROUP BY 1

) ssq

LEFT JOIN (

SELECT

date_trunc('day', BLOCK_TIMESTAMP) AS time,

COUNT(DISTINCT INSTRUCTIONS[0]:programId) AS unique_prog

FROM solana.core.fact_transactions

GROUP BY 1

ORDER BY 1

) t2 ON t2.time = ssq.time

WHERE ssq.time >= '2022-01-01'

ORDER BY 1 DESC