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)

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

group by tx_receiver

order by transactions desc

limit 20