with contracts as (select
events.block_timestamp as date,
receiver_id as receiver,
row_number() over (partition by receiver_id order by events.block_timestamp asc) as nums
from near.core.fact_actions_events events
join near.core.fact_receipts rec
on rec.tx_hash = events.tx_hash
where action_name ilike '%DEPLOYCONTRACT%'
AND events.block_timestamp < CURRENT_DATE
group by receiver, date
qualify nums = 1)
select
count(distinct tx_hash) as transactions,
tx_receiver as contract_address
from near.core.fact_transactions tr
join (select date, receiver, nums
from contracts) as contract
on contract.receiver = tr.tx_receiver
where tr.block_timestamp < CURRENT_DATE
group by tx_receiver
order by transactions desc
limit 10