alirsop-opt-06
    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
    date_trunc('day', block_timestamp) 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 vol_to_op_usd DESC) as rank
    FROM optimism.core.fact_event_logs a JOIN price ON a.block_timestamp::DATE = price.day
    LEFT JOIN optimism.core.dim_contracts l ON a.EVENT_INPUTS:tokenIn = l.address
    WHERE event_name = 'Swap' and symbol is not null and date>='2022-06-04' and date<='2022-11-01'

    AND EVENT_INPUTS:tokenOut='0x4200000000000000000000000000000000000042' -- OP TOKEN
    AND tx_status = 'SUCCESS'
    GROUP BY 1, 2, 3,4
    qualify rank <= 10
    -- ORDER by 1
    Run a query to Download Data