min(block_timestamp) as deployed_date,

contract_address

FROM avalanche.core.fact_event_logs

GROUP by 2

)

date_trunc('day', deployed_date)::date as date,

CASE

when date >= '2023-05-05' then 'After Announcement'

else 'Before Announcement' end as period,

LABEL_TYPE as "Contract Type",

COUNT(DISTINCT contract_address) as "Contract Count",

COUNT(DISTINCT (CASE when NOT LABEL_TYPE is NULL then contract_address end)) as "Contract Cnt",

sum("Contract Count") over (partition by "Contract Type" order by date) as "Cumulative Contract Count"

FROM deployDate LEFT outer JOIN avalanche.core.dim_labels ON address = contract_address

WHERE date >= '2023-01-01'

AND date <= CURRENT_DATE - 1

GROUP by 1,2,3

ORDER by 1