select

block_timestamp as order_request_time

, tx_hash as order_request_tx

, max(CASE WHEN flattened.key = 'requester' THEN flattened.value else null end) as user

, max(CASE WHEN flattened.key = 'id' THEN flattened.value else null end) as id

, max(case when event_name = 'OrderRequested' then TO_VARIANT(decoded_log:"order") else null end) as order_request_deets

, max(case when event_name = 'UserOperationSponsored' then TO_VARIANT(decoded_log) else null end) as user_sponsored_operation

from

arbitrum.core.ez_decoded_event_logs,

LATERAL FLATTEN(input => decoded_log) AS flattened

WHERE

flattened.key IS NOT NULL

AND flattened.value IS NOT NULL

and origin_from_address = '0xaa0ed80de46cf02bde4493a84fe22af8fe79c01f'

and origin_to_address = '0xc498e2715a9180e490bb9e6776acdcbaeb748cc7'

and event_name in ('OrderRequested', 'UserOperationSponsored')

group by 1, 2

),

all_order_fills as (

select

block_timestamp as order_fill_time

, tx_hash as order_fill_tx

, DECODED_LOG:requester as user

, DECODED_LOG:id as id

, DECODED_LOG as order_fill_deets

from

arbitrum.core.ez_decoded_event_logs

where

origin_from_address = '0x2bf22fd411c71b698bf6e0e937b1b948339ec369'

and event_name in ('OrderFill', 'OrderCancelled')

),

missing_tokens_from_table as (

select * from

( values

('18', 'AMD.d', '0xd8f728adb72a46ae2c92234ae8870d04907786c5')