date_trunc('day',RECORDED_HOUR) AS date1,

avg(close) AS price

from crosschain.core.fact_hourly_prices

where ID = 'near'

group by 1

)

select

block_timestamp::date as date,

price as "Near Price",

count(DISTINCT tx_signer) as "Number of Active Users",

count(DISTINCT tx_hash) as "Number of Txs",

count(DISTINCT TX_RECEIVER) as "Number of Smart Contracts",

sum("Number of Active Users") over (order by date) as "Cumulative Number of Active Users",

sum("Number of Txs") over (order by date) as "Cumulative Number of Txs",

sum("Number of Smart Contracts") over (order by date) as "Cumulative Number of Smart Contracts",

sum("Near Price"*(TRANSACTION_FEE/1e24)) as "Total Tx Fee in USD",

sum((TRANSACTION_FEE/1e24)) as "Total Tx Fee (NEAR)",

sum(GAS_USED/1e12) as "Total Gas Used"

from near.core.fact_transactions a join price b on date_trunc('day',a.block_timestamp)=b.date1

where TX_STATUS = 'Success'

group by 1,2

order by 1