WITH swaps AS (

SELECT

BLOCK_TIMESTAMP::DATE AS swap_date,

'RON_TO_USDC' AS swap_type,

TX_HASH,

DECODED_LOG:_amount1In / POWER(10, 18) AS ron_amount,

DECODED_LOG:_amount0Out / POWER(10, 6) AS usdc_amount

FROM ronin.core.ez_decoded_event_logs

WHERE CONTRACT_ADDRESS = '0x4f7687affc10857fccd0938ecda0947de7ad3812'

AND EVENT_NAME = 'Swap'

AND TOPIC_0 = '0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822'

AND DECODED_LOG:_amount1In > 0

AND DECODED_LOG:_amount0Out > 0

UNION ALL

-- USDC to RON swaps (_amount0In is USDC, _amount1Out is RON)

SELECT

BLOCK_TIMESTAMP::DATE AS swap_date,

'USDC_TO_RON' AS swap_type,

TX_HASH,

DECODED_LOG:_amount1Out / POWER(10, 18) AS ron_amount,

DECODED_LOG:_amount0In / POWER(10, 6) AS usdc_amount

FROM ronin.core.ez_decoded_event_logs

WHERE CONTRACT_ADDRESS = '0x4f7687affc10857fccd0938ecda0947de7ad3812'

AND EVENT_NAME = 'Swap'

AND TOPIC_0 = '0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822'

AND DECODED_LOG:_amount0In > 0

AND DECODED_LOG:_amount1Out > 0

),

daily_prices AS (

SELECT

hour::date as date_price,