select

TX_RECEIVER,min(t.block_timestamp) as "Date"

from near.core.fact_transactions t

inner join near.core.fact_actions_events d on t.tx_hash = d.tx_hash

inner join near.core.fact_actions_events_function_call f on t.tx_hash = f.tx_hash

where METHOD_NAME = 'new' and d.action_name = 'DeployContract'

group by TX_RECEIVER

) select

"Date"::date as "Day" ,

date_trunc(month , "Date") as "Month",

extract(hour from "Date") as "Hour" ,

dayname("Day") as "Week" ,

concat("Hour"::string,' - ', ("Hour"+1)::string) as "Hour Range",

count(1) as "# Deployed Contract",

sum("# Deployed Contract") over(order by "Day") as "Cumolative Deployed Contracts",

case

when "Day" between '2022-09-11' and '2022-09-14' then 'NEAR Conf'

else null end as "Events"

from contract_deploy_date group by "Month","Week","Day","Hour" order by "Month","Week","Day","Hour"