WITH kaito_write_txn as

select tx_hash from base.core.fact_transactions

where block_timestamp >= '2025-01-05'

and from_address='0xdeee2a0118de2515b22eda764582dea830c5432c'

and to_address='0x4200000000000000000000000000000000000021'

and substr(input_data,0,10)='0x44adc90e'

,temp AS (

SELECT

tx_hash, block_timestamp,

regexp_substr_all(SUBSTR(input_data, 11), '.{64}') AS segmented,

concat('0x', segmented[3]) AS schema,

utils.udf_hex_to_int(segmented[5]) AS records,

ARRAY_SLICE(segmented, 6 + utils.udf_hex_to_int(segmented[5]), 1000) AS data_array

FROM base.core.fact_transactions

WHERE tx_hash in (select tx_hash from kaito_write_txn)

AND block_timestamp >= '2025-01-05'

),

exploded AS (

SELECT

tx_hash,

block_timestamp,

value,

INDEX AS element_index -- Use FLATTEN()'s native index column for order

FROM temp,

LATERAL FLATTEN(input => data_array)

),

grouped AS (

SELECT

tx_hash,

block_timestamp,

value,

element_index,