bluewaters_analytics2024-05-22 01:12 PM
    Updated 2024-05-22
    WITH TokenPrices AS (
    SELECT
    date_trunc('d', hour) as date,
    median(price) as price
    FROM aptos.price.ez_hourly_token_prices
    WHERE symbol = 'APT'
    AND date >= current_date - interval '30 day'
    GROUP BY 1
    ),
    main AS (
    SELECT
    c.block_timestamp,
    c.gas_used,
    c.gas_unit_price,
    ((c.gas_used * c.gas_unit_price)/1e8) as gas_fee_apt,
    ((c.gas_used * c.gas_unit_price)/1e8) * b.price as gas_fee_usd
    FROM aptos.core.fact_events a
    JOIN aptos.core.fact_transactions c ON a.block_timestamp = c.block_timestamp AND a.tx_hash = c.tx_hash
    LEFT JOIN TokenPrices b ON date_trunc('d', c.block_timestamp) = b.date
    WHERE a.event_address = '0x4bf51972879e3b95c4781a5cdcb9e1ee24ef483e7d22f2d903626f126df62bd1'
    AND c.block_timestamp >= current_date - interval '30 day'
    )
    SELECT
    date_trunc('day', block_timestamp) as date,
    percentile_cont(0.95) within group (order by gas_fee_apt) as "95th Percentile Gas Fee (APT)",
    percentile_cont(0.95) within group (order by gas_fee_usd) as "95th Percentile Gas Fee (USD)",
    percentile_cont(0.99) within group (order by gas_fee_apt) as "99th Percentile Gas Fee (APT)",
    percentile_cont(0.99) within group (order by gas_fee_usd) as "99th Percentile Gas Fee (USD)"
    FROM main
    GROUP BY 1
    ORDER BY 1 DESC;

    QueryRunArchived: QueryRun has been archived