efer10. Monthly TPM - Solana
Updated 2022-12-12
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('minute', BLOCK_TIMESTAMP) AS date_minute,
COUNT(*) AS txns
FROM solana.core.fact_transactions
WHERE BLOCK_TIMESTAMP >= '2022-01-01'
GROUP BY date_minute
ORDER BY date_minute DESC
), tpm AS (
SELECT
DATE_TRUNC('month', date_minute) AS date_month,
MONTHNAME(date_month) AS month_name,
avg(txns) AS TPM
FROM transactions
GROUP BY date_month
), tpm_average AS (
SELECT avg(TPM) AS average FROM tpm
)
SELECT
date_month,
month_name,
TPM,
avg(tpm) OVER (ORDER BY date_month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MA2,
(SELECT average FROM tpm_average)
FROM tpm
/*
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'
Run a query to Download Data