alirsop-opt-04-B
    Updated 2022-11-03
    WITH price AS (
    SELECT
    hour::DATE AS day
    ,AVG(price) AS OP_price
    FROM optimism.core.fact_hourly_token_prices
    WHERE symbol = 'OP'
    GROUP BY 1
    )
    SELECT
    block_timestamp::date AS date,case when date>='2022-06-04' and date<='2022-08-17' then 'Before hack fake rumors'
    when date>'2022-08-17' and date<='2022-08-24' then 'A few daysAfter hack fake rumors(1 Week)'
    else 'After hack fake rumors' end as Time ,SYMBOL ,OP_price
    ,Count(DISTINCT tx_hash) AS Swap_Count
    ,Count(DISTINCT origin_from_address) AS Swapper_Count
    ,sum((CASE WHEN EVENT_INPUTS:tokenOut='0x4200000000000000000000000000000000000042' THEN EVENT_INPUTS:amountIn/1e18 END)*OP_price) as vol_to_op_usd
    ,row_number() over (partition by date order by Swap_Count DESC) as ranking
    FROM optimism.core.fact_event_logs log JOIN price ON block_timestamp::DATE = price.day
    LEFT JOIN optimism.core.dim_contracts cont ON log.EVENT_INPUTS:tokenin = cont.address
    WHERE event_name = 'Swap'
    and EVENT_INPUTS:tokenOut='0x4200000000000000000000000000000000000042'
    AND tx_status = 'SUCCESS' and symbol is not null and date>='2022-06-04' and date<='2022-11-01'
    GROUP BY 1, 2, 3,4
    qualify ranking <= 10

    Run a query to Download Data