0xHaM-dQuarterly Number of Transactions in 2023
Updated 2024-07-14
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
›
⌄
-- forked from: https://flipsidecrypto.xyz/Kaka/q/eJyB1btiVSFa/quarterly-number-of-transactions-in-2023
select
date_trunc('quarter', block_timestamp) as "Date",
case
when year(block_timestamp) = '2023' then concat('Q', quarter("Date"),'-','Months ','[',month("Date") ,' To ',month("Date") + 2,']','-', 23 )
when year(block_timestamp) = '2024' then concat('Q', quarter("Date"),'-','Months ','[',month("Date") ,' To ',month("Date") + 2,']','-', 24 )
end as "Quarter",
CASE when year(block_timestamp) = '2023' then 'y.2023' else 'y.2024' end as year,
count(distinct tx_id) as "Transaction Count",
round("Transaction Count" / count(distinct block_timestamp::date)) as "Average Daily TXs",
count(distinct authorizers[0]) as "User Count",
round("User Count" / count(distinct block_timestamp::date)) as "Average Daily Users",
round((("Transaction Count" / lag("Transaction Count") over (order by "Date")) - 1) * 100) AS "Change in TXs in Percent",
round((("User Count" / lag("User Count") over (order by "Date")) - 1) * 100) AS "Change in Users in Percent",
sum("Transaction Count") over (order by "Date") AS "Total Transaction"
from
flow.core.fact_transactions
where
block_timestamp::date >= '2023-01-01'
AND
block_timestamp::date < '2024-07-01'
GROUP BY 1,2,3
ORDER by 1 DESC
QueryRunArchived: QueryRun has been archived