-- List of unique contract IDs (Top 100 by number of calls)

SELECT receiver_id AS contract_id, COUNT(*) AS num_calls_past90days

FROM (

SELECT *,

ROW_NUMBER() OVER (PARTITION BY tx_hash ORDER BY block_timestamp DESC, block_id DESC) AS rn

FROM near.core.fact_actions_events_function_call

WHERE action_name = 'FunctionCall'

)

WHERE rn = 1 -- Only last call in each transaction

AND block_timestamp >= date_trunc('week', DATE '2024-01-01')

AND block_timestamp < date_trunc('week', DATE '2024-01-08')

GROUP BY receiver_id

ORDER BY COUNT(*) DESC

-- LIMIT 200

),

missing_labels_filled AS (

-- Fill in missing labels for high-call contracts

SELECT

o.contract_id,

o.num_calls_past90days,

CASE o.contract_id

WHEN 'harvest-moon.near' THEN 'Harvest Moon'

WHEN 'aa-harvest-moon.near' THEN 'Harvest Moon'

WHEN 'aa-harvest-moon-relics.near' THEN 'Harvest Moon'

WHEN 'claim.sweat' THEN 'sweat'

WHEN 'meteor.poolv1.near' THEN 'meteor wallet'

WHEN 'meteor-rewards.near' THEN 'meteor wallet'

WHEN 'signin2.sendercommunity.near' THEN 'Sender Network'

WHEN 'token.sendertge.near' THEN 'Sender Network'

WHEN 'senderdaoairdrop01.near' THEN 'Sender Network'

WHEN 'senderdaoairdrop02.near' THEN 'Sender Network'

WHEN 'senderdaoairdrop03.near' THEN 'Sender Network'

WHEN 'senderdaoairdrop04.near' THEN 'Sender Network'

WHEN 'keys.auth.hot.tg' THEN 'Hot Protocol'

WHEN 'mpc.hot.tg' THEN 'Hot Protocol'