MLDZMNdxa6
    Updated 2023-02-22
    with price as (select
    HOUR::date as day,
    SYMBOL,
    decimals,
    avg(price) as token_price
    from ethereum.core.fact_hourly_token_prices
    group by 1,2,3
    ),

    t1 AS (
    SELECT
    swaps.event_index,
    swaps.block_timestamp,
    swaps.tx_hash AS tx,
    CASE
    WHEN swaps.origin_to_address = lower('0x1b02dA8Cb0d097eB8D57A175b88c7D8b47997506') THEN 'SushiSwap'
    WHEN swaps.origin_to_address in('0x68b3465833fb72a70ecdf485e0e4c7bd8665fc45','0xe592427a0aece92de3edee1f18e0157c05861564') THEN 'Uniswap'
    END AS dex,
    swaps.origin_from_address AS swapper,
    swaps.contract_address AS token_contract,
    p.symbol AS token,
    (swaps.event_inputs:value / POW(10, a.decimals) * p.token_price) AS amount
    FROM arbitrum.core.fact_event_logs swaps
    LEFT JOIN arbitrum.core.dim_labels labels ON swaps.origin_to_address = labels.address
    left join arbitrum.core.dim_contracts a on swaps.contract_address=a.ADDRESS
    JOIN price p ON swaps.block_timestamp::date = p.day AND a.SYMBOL = p.SYMBOL
    WHERE
    swaps.tx_hash IN (
    SELECT DISTINCT tx_hash
    FROM arbitrum.core.fact_event_logs
    WHERE tx_status = 'SUCCESS' AND event_name = 'Swap'
    )
    AND swaps.event_name = 'Transfer'
    and a.NAME not ilike '%lp token%' and a.SYMBOL not ilike '%/USD%' and a.SYMBOL not ilike 'LP-%'
    and amount<1e6
    ),
    Run a query to Download Data