SELECT date_trunc('day', block_timestamp::date) as day,
date_trunc('week', block_timestamp::date) as week,
date_trunc('month', block_timestamp::date) as Month,
COUNT(TX_HASH) as Total_Transactions_over_time,
sum(Total_Transactions_over_time) over (order by week) as Cumulative_Transactions_Weekly,
sum(Total_Transactions_over_time) over (order by day) as Cumulative_Transactions_Daily,
sum(Total_Transactions_over_time) over (order by Month) as Cumulative_Transactions_Monthly,
SUM(TRANSACTION_FEE/pow(10,24)) as Total_Fees_over_time,
sum(Total_Fees_over_time) over (order by week) as Cumulative_Fee_Weekly,
sum(Total_Fees_over_time) over (order by day) as Cumulative_Fee_Daily,
sum(Total_Fees_over_time) over (order by month) as Cumulative_Fee_Monthly,
Total_Transactions_over_time / 604800 as TPS_Weekly,
Total_Transactions_over_time / (24*60*60) as TPS_Daily,
Total_Transactions_over_time / (30*24*60*60) as TPS_Monthly,
Count(block_id) as Total_Block_over_time,
sum(Total_Block_over_time) over (order by week) as Cumulative_Block_Weekly,
sum(Total_Block_over_time) over (order by day) as Cumulative_Block_Daily,
sum(Total_Block_over_time) over (order by month) as Cumulative_Block_Monthly,
(7*24*60*60) / (Total_Block_over_time*6*7) as AVG_Block_Time_Weekly,
(24*60*60) / Total_Block_over_time as AVG_Block_Time_Daily,
(30*24*60*60) / (Total_Block_over_time*28*30) as AVG_Block_Time_Monthly
--(Total_Transactions_over_time/Total_Block_over_time) as "Transactions per Block-TPB"
From near.core.fact_transactions
where block_timestamp >= '2022-07-01' and block_timestamp < '2022-10-01'
group by 1 ,2,3
order by 1 asc