0xHaM-dVolume
    Updated 2022-10-25
    with swap as (
    select
    date_trunc('{{Interval}}', block_timestamp)::date as date,
    'Optimism' as network,
    'Uniswap' as dex,
    sum((RAW_AMOUNT/pow(10, decimals)) * price) as tot_swap_vol_usd,
    avg((RAW_AMOUNT/pow(10, decimals)) * price) as avg_swap_vol_usd,
    sum(tot_swap_vol_usd) over (order by date) as Cumu_swap_vol_usd
    from optimism.core.fact_token_transfers b join optimism.core.fact_hourly_token_prices c
    on (b.CONTRACT_ADDRESS = c.token_address and b.block_timestamp::date = c.HOUR::date)
    where tx_hash in ( SELECT tx_hash from optimism.core.fact_event_logs
    where origin_to_address in ('0xe592427a0aece92de3edee1f18e0157c05861564','0x68b3465833fb72a70ecdf485e0e4c7bd8665fc45')
    and event_name = 'Swap'and tx_status = 'SUCCESS')
    and block_timestamp::date >= CURRENT_DATE - 181
    and block_timestamp::date <= CURRENT_DATE - 1
    group by 1

    union

    select
    date_trunc('{{Interval}}', block_timestamp)::date as date,
    'Ethereum' as network,
    'Uniswap' as dex,
    abs(sum(AMOUNT0_USD)) as tot_swap_vol_usd,
    abs(avg(AMOUNT0_USD)) as Average_Swap_Volume,
    sum(tot_swap_vol_usd) over (order by date) as Cumu_swap_vol_usd
    from ethereum.uniswapv3.ez_swaps
    WHERE block_timestamp::date >= CURRENT_DATE - 181
    and block_timestamp::date <= CURRENT_DATE - 1
    group by 1,2,3
    union

    select
    date_trunc('{{Interval}}', block_timestamp)::date as date,
    'Optimism' as network,
    Run a query to Download Data