rezarwzOP volume
    Updated 2022-10-06
    with trade as(
    SELECT
    block_timestamp::Date as date,
    ORIGIN_FROM_ADDRESS,
    tx_hash,
    case
    WHEN EVENT_INPUTS:tokenOut='0x4200000000000000000000000000000000000042' then EVENT_INPUTS:amountOut/pow(10,(SELECT DECIMALS from optimism.core.dim_contracts where symbol='OP'))
    when EVENT_INPUTS:tokenIn='0x4200000000000000000000000000000000000042' then EVENT_INPUTS:amountIn/pow(10,(SELECT DECIMALS from optimism.core.dim_contracts where symbol='OP'))
    end as amount
    from optimism.core.fact_event_logs
    WHERE event_name='Swap' and amount is not null and ORIGIN_FUNCTION_SIGNATURE!='0x'),
    average_price as (
    SELECT
    date(hour) as date,
    avg(price) as price
    FROM optimism.core.fact_hourly_token_prices
    WHERE SYMBOL='OP'
    GROUP by 1
    )
    SELECT
    trade.date,
    price,
    sum(amount) as daily_volume,
    sum(daily_volume) over (order by trade.date) as total_volume,
    COUNT (DISTINCT ORIGIN_FROM_ADDRESS) as daily_traders,
    sum(daily_traders) over (order by trade.date) as total_traders,
    COUNT (DISTINCT tx_hash) as daily_trades,
    sum(daily_trades) over (order by trade.date) as total_trades
    FROM trade inner join average_price a on a.date=trade.date
    GROUP BY trade.date,2
    Run a query to Download Data