ev.block_timestamp,

action_name,

tx.tx_receiver AS contract_address

FROM near.core.fact_actions_events AS ev

LEFT JOIN near.core.fact_transactions AS tx

ON tx.tx_hash = ev.tx_hash

WHERE ev.action_name IN ('DeployContract', 'FunctionCall')

),

dim_contracts AS (

SELECT contract_address,

min(block_timestamp) AS min_action_timestamp,

min(CASE WHEN action_name = 'DeployContract' THEN block_timestamp ELSE NULL END) AS min_deploy_timestamp,

max(CASE WHEN action_name = 'DeployContract' THEN block_timestamp ELSE NULL END) AS max_deploy_timestamp,

min(CASE WHEN action_name = 'FunctionCall' THEN block_timestamp ELSE NULL END) AS min_function_call_timestamp,

max(CASE WHEN action_name = 'FunctionCall' THEN block_timestamp ELSE NULL END) AS max_function_call_timestamp

FROM contract_txs

GROUP BY 1

),

dim_contracts__filled_gaps AS (SELECT *,

coalesce(least(min_deploy_timestamp, min_function_call_timestamp),

min_deploy_timestamp,

min_function_call_timestamp) AS earliest_timestamp_available,

(CASE WHEN min_deploy_timestamp <= min_function_call_timestamp THEN 'new' ELSE 'old' END) AS contract_type_at_earliest_timestamp,

(CASE WHEN contract_type_at_earliest_timestamp = 'new' THEN earliest_timestamp_available

WHEN contract_type_at_earliest_timestamp = 'old' THEN '2021-08-31 00:00:00' END) AS inferred_start_timestamp

FROM dim_contracts

SELECT count(*) AS total_contracts,

count(CASE WHEN min_deploy_timestamp >= '2022-01-01 00:00:00.000'

AND contract_type_at_earliest_timestamp = 'new'

THEN contract_address ELSE NULL END) AS deployed_contracts_2022

FROM dim_contracts__filled_gaps