thejoyceprior-beige
Updated 2024-09-01
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
›
⌄
WITH daily_transactions AS (
SELECT
DATE_TRUNC('day', BLOCK_TIMESTAMP) AS time_,
COUNT(DISTINCT TX_HASH) AS txns
FROM
base.core.fact_transactions
WHERE
BLOCK_TIMESTAMP BETWEEN '2024-08-01' AND '2024-08-31'
GROUP BY
1
ORDER BY
1 DESC
)
SELECT
time_,
txns,
ROUND(AVG(txns) OVER (ORDER BY time_ ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS "7d_avg"
FROM
daily_transactions
ORDER BY
time_ DESC;