WITH contract_txs AS (

SELECT ev.tx_hash

, 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