SELECT

block_timestamp,

tx_hash,

ORIGIN_FROM_ADDRESS,

CASE

WHEN CAST(decoded_log:_amount0In AS INTEGER) > 0

THEN CAST(decoded_log:_amount0In AS INTEGER) / POWER(10, 6)

ELSE CAST(decoded_log:_amount0Out AS INTEGER) / POWER(10, 6)

END AS amount0_used

FROM ronin.core.ez_decoded_event_logs

WHERE

contract_address = '0x4f7687affc10857fccd0938ecda0947de7ad3812'

AND origin_to_address = '0x5f0acdd3ec767514ff1bf7e79949640bf94576bd'

AND origin_function_signature = '0x3593564c'

AND event_name = 'Swap'

SELECT

COUNT(DISTINCT tx_hash) AS distinct_tx_count,

SUM(amount0_used) AS total_amount_used,

COUNT(DISTINCT origin_from_address) AS distinct_senders,

AVG(amount0_used) AS average_volume,

SUM(CASE

WHEN block_timestamp >= DATEADD('day', -1, CURRENT_TIMESTAMP)

THEN amount0_used

ELSE 0

END) AS volume_last_24h

FROM base;