feyikemiAptos DEX
    Updated 2024-04-25
    -- Credits to 0xHaM-d, his query on 'Top Projects Based on Transactions' helped put this query together
    WITH Price as (
    SELECT
    hour :: date as p_date,
    symbol,
    avg(PRICE) as usd_price
    FROM
    aptos.price.ez_hourly_token_prices
    WHERE
    symbol = 'APT'
    GROUP BY 1, 2
    ),

    Fee AS (
    SELECT
    date(block_timestamp) AS Date,
    Gas_used,
    label_type,
    INITCAP(Label) as DEXs,
    tx_hash,
    Sender,
    ((gas_used * gas_unit_price) / 1e8) as Gas_Fee,
    FROM
    aptos.core.fact_events a
    JOIN aptos.core.dim_labels b on address = event_address
    JOIN aptos.core.fact_transactions c using(tx_hash)
    WHERE
    b.label_type = 'dex'
    AND SUCCESS = true
    AND tx_type = 'user_transaction'
    AND date > current_date - 30
    ),
    Final AS (
    SELECT
    date,
    QueryRunArchived: QueryRun has been archived