SalehNEAR Protocol: Weekly Transactions copy
Updated 2024-07-11
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
›
⌄
-- forked from Ericmoore_1 / NEAR Protocol: Weekly Transactions @ https://flipsidecrypto.xyz/Ericmoore_1/q/k6yA1_UvP_xx/near-protocol-weekly-transactions
WITH Weekly_stats AS (
SELECT
Date_trunc('week', block_timestamp) as Week,
COUNT(distinct tx_signer) as Active_users,
COUNT(distinct tx_hash) as Transactions
FROM near.core.fact_transactions
WHERE block_timestamp >= '2024-05-01'
AND block_timestamp < current_date
GROUP BY 1
)
SELECT
Week,
Active_users,
Transactions,
Transactions/604800 as transactions_per_second,
LAG(active_users) OVER (ORDER BY week) as active_users_last_week,
LAG(transactions) OVER (ORDER BY week) as transactions_last_week,
((transactions - LAG(transactions) OVER (ORDER BY week))/LAG(transactions) OVER (ORDER BY week))*100 as transactions_diff,
((active_users - LAG(active_users) OVER (ORDER BY week))/LAG(active_users) OVER (ORDER BY week))*100 as active_users_diff
FROM
Weekly_stats
ORDER BY
Week DESC
QueryRunArchived: QueryRun has been archived