SELECT

hour::DATE AS op_date

,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

,OP_price

,SYMBOL

,Count(DISTINCT tx_hash) AS swaps_cnt

,Count(DISTINCT origin_from_address) AS uniq_swapper_cnt

,sum((CASE WHEN EVENT_INPUTS:tokenIn='0x4200000000000000000000000000000000000042' THEN EVENT_INPUTS:amountIn/1e18 END)*OP_price) as vol_from_op_usd

,row_number() over (partition by date order by vol_from_op_usd DESC) as rank

,sum(swaps_cnt) over (partition by SYMBOL order by date) as cum_swaps_tx

,sum(vol_from_op_usd) over (partition by SYMBOL order by date) as cum_vol_from_op_usd

FROM optimism.core.fact_event_logs a JOIN op_price op ON a.block_timestamp::DATE = op.op_date

LEFT JOIN optimism.core.dim_contracts l ON a.EVENT_INPUTS:tokenOut = l.address

WHERE event_name = 'Swap'

and EVENT_INPUTS:tokenIn='0x4200000000000000000000000000000000000042'

AND tx_status = 'SUCCESS'

GROUP BY 1, 2, 3

qualify rank <= 10