SELECT *,

CASE

WHEN origin_to_address = '0xb0be5d911e3bd4ee2a8706cf1fac8d767a550497' THEN 'bathETH'

WHEN origin_to_address = '0x7571cc9895d8e997853b1e0a1521ebd8481aa186' THEN 'bathWBTC'

WHEN origin_to_address = '0xe0e112e8f33d3f437d1f895cbb1a456836125952' THEN 'bathUSDC'

WHEN origin_to_address = '0x60daec2fc9d2e0de0577a5c708bcadba1458a833' THEN 'bathDAI'

WHEN origin_to_address = '0xffbd695bf246c514110f5dae3fa88b8c2f42c411' THEN 'bathUSDT'

WHEN origin_to_address = '0xeb5f29afaaa3f44eca8559c3e8173003060e919f' THEN 'bathSNX'

WHEN origin_to_address = '0x574a21fe5ea9666dbca804c9d69d8caf21d5322b' THEN 'bathOP'

END as pool_name,

date_trunc('HOUR', block_timestamp) as hour,

event_inputs:reward as op_amount,

event_inputs:user as user_wallet

FROM optimism.core.fact_event_logs

WHERE origin_to_address IN ('0xb0be5d911e3bd4ee2a8706cf1fac8d767a550497', '0x7571cc9895d8e997853b1e0a1521ebd8481aa186',

'0xe0e112e8f33d3f437d1f895cbb1a456836125952', '0x60daec2fc9d2e0de0577a5c708bcadba1458a833',

'0xffbd695bf246c514110f5dae3fa88b8c2f42c411', '0xeb5f29afaaa3f44eca8559c3e8173003060e919f',

'0x574a21fe5ea9666dbca804c9d69d8caf21d5322b')

AND event_name = 'RewardPaid'

AND block_timestamp > CURRENT_DATE - 30

), ez_rewards as (

SELECT a.*,

a.op_amount / pow(10, b.decimals) * b.price as reward_usd_amount

FROM rewards_events as a JOIN optimism.core.fact_hourly_token_prices as b ON a.hour = b.hour AND b.token_address = '0x4200000000000000000000000000000000000042'

SELECT block_timestamp::date as date,

pool_name,

sum(reward_usd_amount) AS "Rewards USD Volume"

FROM ez_rewards

GROUP BY date, pool_name