SELECT

DATE_TRUNC('day', a.BLOCK_TIMESTAMP) AS day,

COUNT(DISTINCT a.TO_ADDRESS) AS daily_contract_deploys

FROM boba.core.fact_traces a

WHERE

a.BLOCK_TIMESTAMP >= DATEADD('year', -1, CURRENT_DATE)

AND a.type LIKE '%CREATE%'

AND a.to_address IS NOT NULL

AND a.input IS NOT NULL

AND a.input != '0x'

GROUP BY 1

SELECT

day,

daily_contract_deploys,

SUM(daily_contract_deploys) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

AS cumulative_contract_deploys

FROM daily_contracts

ORDER BY day DESC;