winnie-fsAptos weekly 1 copy
Updated 2024-08-01
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
›
⌄
-- forked from cristinatinto / Aptos weekly 1 @ https://flipsidecrypto.xyz/cristinatinto/q/0dT62bD2r-O9/aptos-weekly-1
with
final as (
SELECT
trunc(block_timestamp,'week') as week,
count(distinct tx_hash) as total_transactions,
sum(total_transactions) over (order by week) as cum_transactions
from aptos.core.fact_transactions x where block_timestamp<current_date
group by 1
order by 1 asc
),
final2 as (
SELECT
trunc(block_timestamp,'week') as week,
count(distinct tx_hash) as total_transactions,
sum(total_transactions) over (order by week) as cum_transactions
from aptos.core.fact_transactions x
where trunc(block_timestamp,'week')<trunc(current_date,'week')-interval '1 WEEK'
group by 1
order by 1 asc
),
final_week as (select * from final order by 1 desc limit 1),
final_past_week 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_week as final join final_past_week as final2
-- asdlfkjqeh
--asdflkj
QueryRunArchived: QueryRun has been archived