kentophilosophical-cyan
Updated 2024-10-26
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
›
⌄
SELECT
TO_CHAR(TO_DATE(BLOCK_TIMESTAMP_HOUR), 'DD') ||
CASE
WHEN TO_CHAR(TO_DATE(BLOCK_TIMESTAMP_HOUR), 'DD') IN ('01', '21', '31') THEN 'st'
WHEN TO_CHAR(TO_DATE(BLOCK_TIMESTAMP_HOUR), 'DD') IN ('02', '22') THEN 'nd'
WHEN TO_CHAR(TO_DATE(BLOCK_TIMESTAMP_HOUR), 'DD') IN ('03', '23') THEN 'rd'
ELSE 'th'
END AS day,
(SUM(TRANSACTION_COUNT) / SUM(BLOCK_COUNT)) AS avg_tx_per_block
FROM cosmos.stats.ez_core_metrics_hourly
WHERE EXTRACT(MONTH FROM BLOCK_TIMESTAMP_HOUR) = 9
AND EXTRACT(YEAR FROM BLOCK_TIMESTAMP_HOUR) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY TO_DATE(BLOCK_TIMESTAMP_HOUR)
ORDER BY TO_DATE(BLOCK_TIMESTAMP_HOUR);
QueryRunArchived: QueryRun has been archived