SELECT

date_trunc('hour',block_timestamp) as time,

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'),

price as(

SELECT

hour,

(price)

FROM optimism.core.fact_hourly_token_prices

WHERE SYMBOL='OP'

sum(amount) as total_volume,

WHEN price>0 and price<0.5 then 'The price of the token is between 0 and 0.5 dollars.'

WHEN price>=0.5 and price<1 then 'The price of the token is between 0.5 dollars and one 1 dollar.'

WHEN price>=1 and price<1.5 then 'The price of the token is between 1 dollar and 1.5 dollars.'

WHEN price>=1.5 and price<2 then 'The price of the token is between 1.5 dollars and 2 dollars.'

WHEN price>=2 then 'The token price is equal to or greater than 2 dollars.'

end as Classification

FROM volume v inner join price p on p.hour=v.time

GROUP BY 2