Updated 2024-12-17
    WITH weekly_data AS (
    SELECT
    DATE_TRUNC('week', block_timestamp) AS week_start,
    COUNT(DISTINCT tx_hash) AS weekly_swap_count,
    SUM(amount_in_usd) AS weekly_swap_volume_usd,
    COUNT(DISTINCT origin_from_address) AS weekly_active_users
    FROM kaia.defi.ez_dex_swaps
    WHERE platform = 'capybara'
    GROUP BY week_start
    )
    SELECT
    week_start,
    weekly_swap_count,
    weekly_swap_volume_usd,
    weekly_active_users,
    SUM(weekly_swap_count) OVER (ORDER BY week_start) AS "cumulative swap count",
    SUM(weekly_swap_volume_usd) OVER (ORDER BY week_start) AS "cumulative swap volume usd",
    SUM(weekly_active_users) OVER (ORDER BY week_start) AS "cumulative active users"
    FROM weekly_data
    ORDER BY week_start;

    /*
    WITH stablecoins AS (
    SELECT DISTINCT
    symbol_in AS stable_token
    FROM kaia.defi.ez_dex_swaps
    WHERE symbol_in IN (
    'oUSDT', 'USDC.e', 'KDAI', 'USDC', 'CUSDT', 'mUSDC', 'mUSDT', 'oUSDC', 'oWBTC', 'USDT'
    )
    ),
    swaps AS (
    SELECT
    tx_hash,
    COALESCE(symbol_in, '') AS symbol_in, -- Handle null symbol_in
    COALESCE(symbol_out, '') AS symbol_out, -- Handle null symbol_out
    COALESCE(amount_in_usd, 0) AS amount_in_usd, -- Handle null amounts
    QueryRunArchived: QueryRun has been archived