Elprognerd1 - average txn per day/week/month
Updated 2023-06-04Copy Reference Fork
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
›
⌄
with txs AS (
SELECT
x.BLOCK_TIMESTAMP as BLOCK_TIMESTAMP,
x.TX_ID as tx,
x.PROPOSER as user,
y.EVENT_TYPE as type,
y.EVENT_CONTRACT
FROM flow.core.fact_transactions x JOIN flow.core.fact_events y ON x.TX_ID = y.TX_ID
WHERE x.TX_SUCCEEDED = 'true'
)
SELECT
count( DISTINCT tx)/count( DISTINCT date_trunc('day', BLOCK_TIMESTAMP)) as "Average number of txns per a day",
count( DISTINCT tx)/count( DISTINCT date_trunc('week', BLOCK_TIMESTAMP)) as "Average number of txns per a week",
count( DISTINCT tx)/count( DISTINCT date_trunc('month', BLOCK_TIMESTAMP)) as "Average number of txns per a month",
count( DISTINCT user)/count( DISTINCT date_trunc('day', BLOCK_TIMESTAMP)) as "Average number of active users per a day",
count( DISTINCT user)/count( DISTINCT date_trunc('week', BLOCK_TIMESTAMP)) as "Average number of active users per a week",
count( DISTINCT user)/count( DISTINCT date_trunc('month', BLOCK_TIMESTAMP)) as "Average number of active users per a month"
from txs
Run a query to Download Data