SELECT

DATE_TRUNC('week', BLOCK_TIMESTAMP) AS WeekStart,

COUNT(*) AS TransactionsCount

FROM aptos.core.fact_transactions

GROUP BY DATE_TRUNC('week', BLOCK_TIMESTAMP)

), WeekDurations AS (

SELECT

DATE_TRUNC('week', BLOCK_TIMESTAMP) AS WeekStart,

DATEDIFF(second, MIN(BLOCK_TIMESTAMP), MAX(BLOCK_TIMESTAMP)) AS WeekDurationSeconds

FROM aptos.core.fact_transactions

GROUP BY DATE_TRUNC('week', BLOCK_TIMESTAMP)

SELECT

ROW_NUMBER() OVER (ORDER BY wt.WeekStart) AS "Week No.",

CONCAT(

TO_VARCHAR(wt.WeekStart, 'YYYY-MM-DD'),

' to ',

TO_VARCHAR(DATEADD(second, wd.WeekDurationSeconds, wt.WeekStart), 'YYYY-MM-DD')

) AS "Range",

(wt.TransactionsCount / wd.WeekDurationSeconds) AS "Heart Rate",

TO_CHAR(DATE_TRUNC('month', wt.WeekStart), 'Mon YYYY') AS "Month"

FROM WeeklyTransactions wt

JOIN WeekDurations wd ON wt.WeekStart = wd.WeekStart

ORDER BY "Week No." DESC