nickpayiatis_Top Contracts By Transactions over Time
    Updated 2023-01-27
    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
    block_timestamp::Date as "Date",

    tx_receiver as "Contract Address",
    count(distinct tx_hash) as transactions
    from near.core.fact_transactions tr
    join (select date, receiver, nums
    from contracts) as contract
    on contract.receiver = tr.tx_receiver
    where

    case when '{{date}}' = 'week' then
    tr.block_timestamp::Date >=CURRENT_DATE-8
    and tr.block_timestamp::date < Current_date

    else

    tr.block_timestamp::Date >=CURRENT_DATE-31
    and tr.block_timestamp::date < Current_date
    end
    Run a query to Download Data