SELECT

tx_hash, block_timestamp, action_name

FROM near.core.fact_actions_events

WHERE action_name = 'DeployContract'

AND block_timestamp :: date >= '2022-01-01'

),

new_deploy AS (

SELECT

nd.tx_hash, nd.block_timestamp

FROM near.core.fact_actions_events_function_call nd

WHERE method_name = 'new'

AND block_timestamp :: date >= '2022-01-01'

),

link AS (

SELECT

d.tx_hash, d.block_timestamp

FROM deploy d

JOIN new_deploy

ON d.tx_hash = new_deploy.tx_hash

),

contracts AS (

SELECT

tx.tx_hash, tx.block_timestamp, tx.tx_receiver AS contract, row_number() over(partition by tx.tx_receiver order by tx.block_timestamp) as row_num

FROM near.core.fact_transactions tx

JOIN link

ON tx.tx_hash = link.tx_hash

)

SELECT COUNT(DISTINCT contract) AS total_contract

from contracts

WHERE row_num = 1

LIMIT 100