emiTotal CEX Inflow
    Updated 2023-05-24
    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_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_to.label_type = 'cex'
    ),
    transfer_summary AS (
    SELECT
    DATE_TRUNC('month', date) AS month,
    origin_from_address,
    COUNT(DISTINCT origin_from_address) AS num_users,
    SUM(amount_usd) AS total_usd_volumes,
    SUM(fee_usd) AS total_fees,
    COUNT(*) AS total_transactions
    FROM transfers
    GROUP BY month, origin_from_address
    ),
    Run a query to Download Data