adriaparcerisasflow stats: monthly txs
Updated 2024-12-22
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
›
⌄
with
txs as (
select distinct block_timestamp,tx_id
from flow.core.fact_transactions
UNION
select distinct block_timestamp, tx_hash as tx_id
from flow.core_evm.fact_transactions
),
final as (
SELECT
trunc(block_timestamp,'month') as month,
count(distinct tx_id) as total_transactions,
sum(total_transactions) over (order by month) as cum_transactions
from txs x
group by 1
having month<trunc(current_date,'month')
order by 1 asc
),
final2 as (
SELECT
trunc(block_timestamp,'month') as month,
count(distinct tx_id) as total_transactions
from txs x
where block_timestamp<current_date-interval '1 month'
group by 1
having month<trunc(current_date,'month')
order by 1 asc
),
final_month as (select * from final order by 1 desc limit 1),
final_past_month as (select * from final2 order by 1 desc limit 1)
select
final.*,concat(final.total_transactions,' (',final.total_transactions-final2.total_transactions,')') as transactions_diff, ((final.total_transactions-final2.total_transactions)/final2.total_transactions)*100 as pcg_diff
from final_month as final join final_past_month as final2
QueryRunArchived: QueryRun has been archived