flipside

    weekly change percent
    datavortexweekly change percent
    Updated 2025-01-17
    /*
    WITH token_transfers AS (
    SELECT
    ACCOUNT_ADDRESS AS holder,
    DATE_TRUNC('week', block_timestamp) AS transfer_week,
    CASE
    WHEN transfer_event = 'DepositEvent' THEN AMOUNT
    WHEN transfer_event = 'WithdrawEvent' THEN -AMOUNT
    END AS amt
    FROM aptos.core.fact_transfers
    WHERE TOKEN_ADDRESS = '0xe88ae9670071da40a9a6b1d97aab8f6f1898fdc3b8f1c1038b492dfad738448b::coin::Donk'
    ),
    weekly_balances AS (
    SELECT
    holder,
    transfer_week,
    SUM(amt) AS weekly_balance
    FROM token_transfers
    GROUP BY holder, transfer_week
    HAVING SUM(amt) > 0
    ),
    weekly_holders AS (
    SELECT
    transfer_week,
    COUNT(DISTINCT holder) AS weekly_holders
    FROM weekly_balances
    GROUP BY transfer_week
    ),
    weekly_change AS (
    SELECT
    transfer_week,
    weekly_holders,
    weekly_holders - LAG(weekly_holders) OVER (ORDER BY transfer_week) AS holders_change
    FROM weekly_holders
    )
    SELECT
    QueryRunArchived: QueryRun has been archived