efer4. Hourly Insights - 2
Updated 2023-04-13
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
WITH transactions AS (
SELECT
DATE_TRUNC('hour', BLOCK_TIMESTAMP) AS hour,
COUNT(*) AS txns
FROM flow.core.fact_transactions
WHERE BLOCK_TIMESTAMP >= '2022-01-01'
GROUP BY hour
), min AS (
SELECT
HOUR(hour) AS h,
MIN(txns) AS min,
'Min' AS data_type
FROM transactions
GROUP BY h
), max AS (
SELECT
HOUR(hour) AS h,
MAX(txns) AS max,
'Max' AS data_type
FROM transactions
GROUP BY h
), average AS (
SELECT
HOUR(hour) AS h,
AVG(txns) AS avg,
'AVG' AS data_type
FROM transactions
GROUP BY h
)
SELECT
min.h AS hour,
CONCAT(LPAD(hour, 2, 0), ':00') AS full_hour,
min.min AS min,
max.max AS max,
average.avg AS avg
Run a query to Download Data