SELECT proposer , min(block_timestamp) AS min_date , max(block_timestamp) AS max_date , COUNT(DISTINCT tx_id) AS total_txs

FROM flow.core.fact_transactions

WHERE block_timestamp::date >= '2022-01-01'

AND tx_succeeded = TRUE

GROUP BY 1

),

base2 AS (

SELECT proposer , datediff(MINUTE , min_date , max_date) AS MINUTEs_count , MINUTEs_count / total_txs AS time_between_txs

FROM base

SELECT CASE

WHEN time_between_txs = 0 THEN 'Less Then 1 Min'

WHEN time_between_txs BETWEEN 1 AND 30 THEN 'Btw 1 ~ 30 Min'

WHEN time_between_txs BETWEEN 30 AND 60 THEN 'Btw 30 Min And 1 Hour'

WHEN time_between_txs BETWEEN 60 AND 300 THEN 'Btw 1 ~ 5 Hour'

WHEN time_between_txs BETWEEN 300 AND 600 THEN 'Btw 5 ~ 10 Hour'

WHEN time_between_txs BETWEEN 600 AND 1440 THEN 'Btw 10 Hour And 1 Day'

WHEN time_between_txs BETWEEN 1440 AND 4320 THEN 'Btw 1 ~ 3 Day'

WHEN time_between_txs BETWEEN 4320 AND 7200 THEN 'Btw 3 ~ 5 Day'

WHEN time_between_txs BETWEEN 7200 AND 10080 THEN 'Btw 5 Day And 1 Week'

WHEN time_between_txs BETWEEN 10080 AND 20160 THEN 'Btw 1 ~ 2 Week'

WHEN time_between_txs BETWEEN 20160 AND 30240 THEN 'Btw 2 ~ 3 Week'

WHEN time_between_txs BETWEEN 30240 AND 40320 THEN 'Btw 3 Week And 1 Month'

WHEN time_between_txs BETWEEN 40320 AND 80640 THEN 'Btw 1 ~ 2 Month'

WHEN time_between_txs BETWEEN 80640 AND 120960 THEN 'Btw 2 ~ 3 Month'

ELSE 'More Then 3 Month' END AS average_time_btw_txs ,

COUNT (DISTINCT proposer) AS users_count,

(SELECT (sum(time_between_txs) / COUNT(DISTINCT proposer ))/1440 FROM base2)AS Overall_average_time_in_chain_day ,

(SELECT (sum(time_between_txs) / COUNT(DISTINCT proposer ))/43200 FROM base2)AS Overall_average_time_in_chain_month

FROM base2

GROUP BY 1