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",

AVG("Number of Active Contracts") over (

ORDER BY

date ROWS BETWEEN 7 PRECEDING

AND CURRENT ROW

) as "Avarage Daily (7D)",

AVG("Number of Active Contracts") over (

ORDER BY

date ROWS BETWEEN 30 PRECEDING

AND CURRENT ROW

) as "Avarage Daily (30D)"

(

-- 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'

AND block_timestamp >= current_date - 90 QUALIFY ROW_NUMBER() OVER (

PARTITION BY tx_hash

ORDER BY

block_timestamp DESC,

block_id DESC

) = 1

)

GROUP BY

1

ORDER BY

1 DESC