flipside

    Aerodrome Swap Metrics by Pool copy
    jackguyAerodrome Swap Metrics by Pool copy
    Updated 2023-09-02
    -- forked from Aerodrome Swap Metrics by Pool @ https://flipsidecrypto.xyz/edit/queries/80593d5d-293b-4e5c-bb79-7582d0ca53d7

    with tab1 as (
    SELECT
    tx_hash
    FROM base.core.fact_transactions
    where to_address LIKE lower('0xcF77a3Ba9A5CA399B7c97c74d54e5b1Beb874E43')
    --GROUP BY 1
    ), tab2 as (

    SELECT
    tx_hash,
    'in' as flow,
    to_address as pool,
    block_timestamp,
    from_address,
    (raw_amount / power(10, decimals)) * price as swap_volume
    FROM base.core.fact_token_transfers
    LEFT outer JOIN
    (
    SELECT
    date(hour) as day,
    token_address,
    symbol,
    decimals,
    median(price) as price
    FROM base.price.ez_hourly_token_prices
    GROUP BY 1,2,3,4
    ) on day = date(block_timestamp)
    and token_address = contract_address
    WHERE tx_hash in (SELECT * from tab1)

    --AND symbol is NULL
    --LIMIT 200

    UNION
    Run a query to Download Data