Crazzy_SidDeFi Activity Overview
    Updated 2024-10-08
    WITH price_data AS (
    SELECT
    token_address,
    DATE_TRUNC('day', MODIFIED_TIMESTAMP) AS day,
    AVG(price) AS avg_price_usd
    FROM aptos.price.ez_prices_hourly
    WHERE MODIFIED_TIMESTAMP >= DATEADD(day, -30, CURRENT_TIMESTAMP)
    GROUP BY token_address, day
    ),
    dex_activities AS (
    SELECT
    DATE_TRUNC('day', block_timestamp) AS day,
    token_in AS token_address,
    SUM(amount_in_USD) AS total_dex_in_usd,
    SUM(amount_out_USD) AS total_dex_out_usd
    FROM aptos.defi.ez_dex_swaps
    WHERE block_timestamp >= DATEADD(day, -30, CURRENT_TIMESTAMP)
    GROUP BY day, token_in
    ),
    bridge_activities AS (
    SELECT
    DATE_TRUNC('day', block_timestamp) AS day,
    token_address,
    SUM(CASE WHEN direction = 'in' THEN amount_unadj / POW(10, 18) ELSE 0 END) AS total_bridge_in_native,
    SUM(CASE WHEN direction = 'out' THEN amount_unadj / POW(10, 18) ELSE 0 END) AS total_bridge_out_native
    FROM aptos.defi.fact_bridge_activity
    WHERE block_timestamp >= DATEADD(day, -30, CURRENT_TIMESTAMP)
    GROUP BY day, token_address
    )
    SELECT
    COALESCE(dex_activities.token_address, bridge_activities.token_address) AS token_address,
    SUM(COALESCE(dex_activities.total_dex_in_usd, 0) + COALESCE(bridge_activities.total_bridge_in_native * COALESCE(p.avg_price_usd, 0), 0)) AS total_in_usd,
    SUM(COALESCE(dex_activities.total_dex_out_usd, 0) + COALESCE(bridge_activities.total_bridge_out_native * COALESCE(p.avg_price_usd, 0), 0)) AS total_out_usd,
    SUM(
    (COALESCE(dex_activities.total_dex_in_usd, 0) + COALESCE(bridge_activities.total_bridge_in_native * COALESCE(p.avg_price_usd, 0), 0)) -
    (COALESCE(dex_activities.total_dex_out_usd, 0) + COALESCE(bridge_activities.total_bridge_out_native * COALESCE(p.avg_price_usd, 0), 0))