emiDEX to CEX Monthly
    Updated 2023-05-23
    -- forked from CEX to DEX Monthly @ https://flipsidecrypto.xyz/edit/queries/43ca67a8-9b15-4406-ac8f-8bebf75c6e75

    -- forked from CEX to DEX Total @ https://flipsidecrypto.xyz/edit/queries/151c2a03-5c40-4464-a68d-5d6c4464c5b0

    WITH eth_price AS (
    SELECT
    DATE_TRUNC('day', a.hour) AS day,
    AVG(a.price) AS price_usd
    FROM ethereum.core.fact_hourly_token_prices a
    WHERE a.symbol = 'WETH'
    GROUP BY 1
    ),
    transfers AS (
    SELECT
    DATE_TRUNC('month', t.block_timestamp) AS date,
    t.origin_from_address,
    t.to_address,
    t.amount_usd,
    ft.tx_fee * ep.price_usd AS fee_usd
    FROM ethereum.core.ez_token_transfers t
    JOIN ethereum.core.fact_transactions ft ON t.tx_hash = ft.tx_hash
    JOIN ethereum.core.dim_labels l_from ON t.from_address = l_from.address
    JOIN ethereum.core.dim_labels l_to ON t.to_address = l_to.address
    JOIN eth_price ep ON DATE_TRUNC('day', t.block_timestamp) = ep.day
    WHERE t.symbol = 'UNI'
    AND t.amount_usd IS NOT NULL
    AND t.amount_usd > 0
    AND ft.status = 'SUCCESS'
    AND l_from.label_type = 'dex' AND l_to.label_type = 'cex'
    ),
    transfer_summary AS (
    SELECT
    DATE_TRUNC('month', date) AS month,
    origin_from_address,
    SUM(amount_usd) AS total_usd_volumes,
    SUM(fee_usd) AS total_fees,
    Run a query to Download Data