winnie-fsAptos weekly 1 copy
    Updated 2024-08-01
    -- 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