datavortexpolygon
    Updated 2024-11-23
    WITH SwapFees AS (
    SELECT
    t.tx_fee AS fee_in_matic,
    COUNT(s.tx_hash) AS number_of_swaps
    FROM
    polygon.core.fact_transactions t
    JOIN
    polygon.defi.ez_dex_swaps s ON t.tx_hash = s.tx_hash
    WHERE
    t.status = 'SUCCESS'
    AND t.block_timestamp >= DATE_TRUNC('month', CURRENT_DATE)
    AND t.block_timestamp < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 MONTH'
    GROUP BY t.tx_fee
    ),
    MATICPrice AS (
    SELECT
    MEDIAN(price) AS matic_price
    FROM
    polygon.price.ez_prices_hourly
    WHERE
    symbol = 'MATIC'
    AND hour >= DATE_TRUNC('month', CURRENT_DATE)
    AND hour < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 MONTH'
    )
    SELECT
    SUM(sf.fee_in_matic) AS total_swap_fees_in_matic,
    SUM(sf.fee_in_matic) * (SELECT mp.matic_price FROM MATICPrice mp) AS total_swap_fees_in_usd,
    SUM(sf.number_of_swaps) AS total_number_of_swaps,
    ROUND(SUM(sf.fee_in_matic) / SUM(sf.number_of_swaps), 4) AS avg_fee_per_swap_in_matic,
    ROUND((SUM(sf.fee_in_matic) / SUM(sf.number_of_swaps)) * (SELECT mp.matic_price FROM MATICPrice mp), 4) AS avg_fee_per_swap_in_usd
    FROM
    SwapFees sf;

    QueryRunArchived: QueryRun has been archived