datavortexWeekly Changes
    Updated 2024-12-17
    WITH weekly_data AS (
    SELECT
    DATE_TRUNC('week', block_timestamp) AS week_start,
    COUNT(DISTINCT origin_from_address) AS weekly_users,
    COUNT(DISTINCT tx_hash) AS weekly_swaps,
    SUM(amount_in_usd) AS weekly_volume_usd
    FROM kaia.defi.ez_dex_swaps
    WHERE platform = 'capybara'
    GROUP BY week_start
    ),
    lagged_data AS (
    SELECT
    week_start,
    weekly_users,
    weekly_swaps,
    weekly_volume_usd,
    LAG(weekly_users) OVER (ORDER BY week_start) AS prev_week_users,
    LAG(weekly_swaps) OVER (ORDER BY week_start) AS prev_week_swaps,
    LAG(weekly_volume_usd) OVER (ORDER BY week_start) AS prev_week_volume,
    ROW_NUMBER() OVER (ORDER BY week_start) AS week_number
    FROM weekly_data
    ),
    weekly_changes AS (
    SELECT
    week_start,
    ROUND((weekly_users - prev_week_users) * 100.0 / NULLIF(prev_week_users, 0), 2) AS user_change_percent,
    ROUND((weekly_swaps - prev_week_swaps) * 100.0 / NULLIF(prev_week_swaps, 0), 2) AS swap_change_percent,
    ROUND((weekly_volume_usd - prev_week_volume) * 100.0 / NULLIF(prev_week_volume, 0), 2) AS volume_change_percent,
    week_number
    FROM lagged_data
    )
    SELECT
    week_start,
    user_change_percent,
    swap_change_percent,
    volume_change_percent
    QueryRunArchived: QueryRun has been archived