efer1. Daily Average Transactions
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
›
⌄
WITH transactions AS (
SELECT
BLOCK_TIMESTAMP::date AS date,
COUNT(*) AS txns
FROM flow.core.fact_transactions
WHERE date >= '2022-01-01'
GROUP BY date
ORDER BY date DESC
), total_average AS (
SELECT avg(txns) AS average FROM transactions
)
SELECT
date,
txns,
avg(txns) OVER (ORDER BY date ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) AS MA10,
SUM(txns) OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative,
(SELECT average FROM total_average) AS average,
CASE
WHEN txns >= average THEN 'Above Average'
ELSE 'Below Average'
END AS type
FROM transactions
ORDER BY date DESC
Run a query to Download Data