shengyi-chiuUntitled Query
    Updated 2022-10-05
    --- all credit to naranjaninja , https://app.flipsidecrypto.com/velocity/queries/5107c468-5a0a-4179-a19d-16292df14191
    with op_price_data as (
    select date_trunc('day', hour) as date,
    avg(price) as op_price
    from optimism.core.fact_hourly_token_prices
    where token_address = '0x4200000000000000000000000000000000000042'
    group by 1
    ),
    sushiswap_data as (
    select date_trunc('day',block_timestamp) as date,
    'Sushiswap' as exchange,
    count(distinct tx_hash) as swaps,
    count(distinct origin_from_address) as users_swapping,
    sum(amount_in_usd) as volume_usd
    from optimism.sushi.ez_swaps
    where block_timestamp >= '2022-05-31' -- first OP price data date available
    and token_in = '0x4200000000000000000000000000000000000042' or token_out = '0x4200000000000000000000000000000000000042'
    group by 1
    ),

    uniswap_data as (
    select date_trunc('day',a.block_timestamp) as date,
    'Uniswap' as exchange,
    count(distinct a.tx_hash) as swaps,
    count(distinct a.origin_from_address) as users_swapping,
    sum((c.price * b.raw_amount)/pow(10, c.decimals)) as volume_usd
    from optimism.core.fact_event_logs a
    join optimism.core.fact_token_transfers b using(tx_hash)
    join optimism.core.fact_hourly_token_prices c on c.token_address = b.contract_address and date_trunc('hour', b.block_timestamp) = c.hour
    where a.event_name = 'Swap'
    and a.origin_to_address in ('0x68b3465833fb72a70ecdf485e0e4c7bd8665fc45','0xe592427a0aece92de3edee1f18e0157c05861564')
    and a.tx_status ='SUCCESS'
    and a.block_timestamp >= '2022-05-31' -- first OP price data date available
    and token_address = '0x4200000000000000000000000000000000000042'
    group by 1