with main as (

select date_trunc('quarter', block_timestamp) as date,

count(DISTINCT creator) as "Creator",

count(contract) as "Contract"

from

(select BLOCK_TIMESTAMP, SIGNER_ID as creator,

RECEIVER_ID as contract, tx_hash,

rank() over (partition by contract order by BLOCK_TIMESTAMP) as rank

from near.core.fact_actions_events

where ACTION_NAME = 'DeployContract'

and block_timestamp :: Date >= '2024-01-01')

where rank=1

group by 1

select

date,

'Q' || concat(quarter(date),' - ',year(date)) as Quarter_name,

"Creator",

"Contract",

100 * ("Creator" - lag("Creator") over (order by date)) / lag("Creator") over (order by date) as "Creator Growth %",

100 * ("Contract" - lag("Contract") over (order by date)) / lag("Contract") over (order by date) as "Contract Growth %"

from main

where date >= '2024-01-01'