WITH active_users as (

-- Find unique users for each contract by day

SELECT

date_trunc('day', block_timestamp) AS date,

COUNT(DISTINCT predecessor_id) AS num_users -- Count unique users

FROM (

-- Select the first function call in each transaction

SELECT *

FROM near.core.fact_actions_events_function_call

WHERE action_name = 'FunctionCall'

AND method_name <> 'new'

AND block_timestamp >= '2024-09-01' and block_timestamp < current_date

QUALIFY ROW_NUMBER() OVER (PARTITION BY tx_hash ORDER BY block_timestamp) = 1

)

GROUP BY 1

),

active_contracts as (

SELECT

date_trunc('day', block_timestamp) as date,

-- The assumption is that the last call in each tx goes to the contract being called

COUNT(DISTINCT RECEIVER_ID) as "Number of Active Contracts"

FROM

(

-- This selects the last function call in each transaction

SELECT

*

FROM

near.core.fact_actions_events_function_call

WHERE

ACTION_NAME = 'FunctionCall'

AND METHOD_NAME <> 'new'