with deploy_contract_list as (
select
tx_hash,
block_timestamp
from near.core.fact_actions_events
where action_name = 'DeployContract'
),
new_deploy_contract_list as (
select
function_call.tx_hash as tx_hash,
function_call.block_timestamp as block_timestamp
from near.core.fact_actions_events_function_call function_call
join deploy_contract_list deploy
on (function_call.tx_hash = deploy.tx_hash
and function_call.block_timestamp = deploy.block_timestamp)
where method_name = 'new'
),
deploy_contract_transactions_list as (
select
tx.tx_receiver,
tx.block_timestamp
from near.core.fact_transactions tx
join new_deploy_contract_list contract
on tx.tx_hash = contract.tx_hash
and tx.block_timestamp = contract.block_timestamp
),
deploy_contract_transactions_all_deploy_contract as (
select
tx_receiver,
block_timestamp,
row_number() over(partition by tx_receiver order by block_timestamp) as row_num
from deploy_contract_transactions_list
),
deploy_contract_transactions_first_deploy_contract as (
select * from deploy_contract_transactions_all_deploy_contract where row_num = 1
)