SELECT

tx_receiver as contract,

a.block_timestamp,

first_value(a.block_timestamp) OVER (PARTITION BY contract ORDER BY a.block_timestamp) as origin,

tx

from near.core.fact_actions_events a

LEFT JOIN near.core.fact_transactions t ON t.tx_hash = a.tx_hash

where action_name = 'DeployContract'

),

refine as(

SELECT

COUNT(DISTINCT contract) as totals,

CASE WHEN block_timestamp = origin THEN 'new' ELSE 'upgrade existing' END as cat,

date_trunc('day', block_timestamp) as ddate

from base

GROUP BY ddate, cat

ORDER BY ddate

SELECT * ,

SUM(totals) OVER (PARTITION BY CAT ORDER BY ddate ASC) as totals_cum

from refine