Updated 2024-11-23
    WITH SwapFees AS (
    SELECT
    t.tx_fee AS fee_in_bnb,
    COUNT(s.tx_hash) AS number_of_swaps
    FROM
    bsc.core.fact_transactions t
    JOIN
    bsc.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
    ),
    BNBPrice AS (
    SELECT
    MEDIAN(price) AS bnb_price
    FROM
    bsc.price.ez_prices_hourly
    WHERE
    symbol = 'BNB'
    AND hour >= DATE_TRUNC('month', CURRENT_DATE)
    AND hour < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 MONTH'
    )
    SELECT
    SUM(sf.fee_in_bnb) AS total_swap_fees_in_bnb,
    SUM(sf.fee_in_bnb) * (SELECT bp.bnb_price FROM BNBPrice bp) AS total_swap_fees_in_usd,
    SUM(sf.number_of_swaps) AS total_number_of_swaps,
    ROUND(SUM(sf.fee_in_bnb) / SUM(sf.number_of_swaps), 4) AS avg_fee_per_swap_in_bnb,
    ROUND((SUM(sf.fee_in_bnb) / SUM(sf.number_of_swaps)) * (SELECT bp.bnb_price FROM BNBPrice bp), 4) AS avg_fee_per_swap_in_usd
    FROM
    SwapFees sf;

    QueryRunArchived: QueryRun has been archived