SELECT DISTINCT event_contract AS new_contract,

MIN(trunc(block_timestamp, 'day')) AS debut

FROM flow.core.fact_events

GROUP BY 1

),

evm_news AS (

SELECT DISTINCT contract AS new_contract,

MIN(trunc(block_timestamp, 'day')) AS debut

FROM (

select x.block_timestamp, x.from_address as creator,y.contract_address as contract

from flow.core_evm.fact_transactions x

join flow.core_evm.fact_event_logs y on x.tx_hash=y.tx_hash

where y.topics[0] ilike '%0x8be0079c531659141344cd1fd0a4f28419497f9722a3daafe3b4186f6b6457e0%'

select x.block_timestamp, x.from_address as creator, x.tx_hash as contract

from flow.core_evm.fact_transactions x

where (x.origin_function_signature='0x60c06040' or x.origin_function_signature='0x60806040') and tx_hash not in (select x.tx_hash

from flow.core_evm.fact_transactions x

join flow.core_evm.fact_event_logs y on x.tx_hash=y.tx_hash

where y.topics[0] ilike '%0x8be0079c531659141344cd1fd0a4f28419497f9722a3daafe3b4186f6b6457e0%')

GROUP BY 1

),

combined_news AS (

SELECT new_contract, debut FROM core_news

UNION ALL

SELECT new_contract, debut FROM evm_news

),

final as (

debut AS date,

COUNT(DISTINCT new_contract) AS new_contracts,

SUM(COUNT(DISTINCT new_contract)) OVER (ORDER BY debut) AS unique_contracts

FROM combined_news where date>'2020-01-01' and date<trunc(current_date,'day')

GROUP BY debut