walemathstotal flow tx
Updated 2024-07-01
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
›
⌄
Montly Flow TransactionWITH previous_month_transactions AS (
SELECT
trunc(block_timestamp, 'month') AS month,
count(distinct tx_id) AS total_transactions
FROM
flow.core.fact_transactions
WHERE
block_timestamp < trunc(current_date, 'month')
GROUP BY
1
),
monthly_transactions AS (
SELECT
current_month.month,
current_month.total_transactions,
CONCAT(current_month.total_transactions, ' (', current_month.total_transactions - previous_month_transactions.total_transactions, ')') AS transactions_diff,
((current_month.total_transactions - previous_month_transactions.total_transactions) / previous_month_transactions.total_transactions) * 100 AS pcg_diff,
SUM(current_month.total_transactions) OVER (ORDER BY current_month.month) AS cum_transactions
FROM
(SELECT
trunc(block_timestamp, 'month') AS month,
count(distinct tx_id) AS total_transactions
FROM
flow.core.fact_transactions
GROUP BY
1) current_month
LEFT JOIN
previous_month_transactions
ON
dateadd(month, -1, current_month.month) = previous_month_transactions.month
ORDER BY
current_month.month DESC
)
SELECT
SUM(total_transactions) AS total_transactions_sum,
SUM(CAST(SPLIT_PART(transactions_diff, ' ', 1) AS INT)) AS transactions_diff_sum,
QueryRunArchived: QueryRun has been archived