Elprognerd2 - month activity copy
Updated 2023-06-04
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
date_trunc('month', BLOCK_TIMESTAMP) as date,
count( DISTINCT tx) as "Number of monthly txns",
sum("Number of monthly txns") over (ORDER by date) as "Cumulative number of txns",
count(DISTINCT user) as "Number of monthly active users",
"Number of monthly txns"/"Number of monthly active users" as "Average number of txns per user"
from txs
GROUP BY 1
ORDER BY 1
Run a query to Download Data