SELECT

LOWER(t1.origin_function_signature) AS origin_function_signature,

t1.tx_hash,

t1.topics,

ROW_NUMBER() OVER (PARTITION BY LOWER(t1.origin_function_signature) ORDER BY t1.block_number ASC) AS row_num

FROM avalanche.core.fact_event_logs t1

WHERE t1.origin_to_address = LOWER('0x18556DA13313f3532c54711497A8FedAC273220E')

),

joined_data AS (

SELECT

ranked_tx_hashes.origin_function_signature,

ranked_tx_hashes.tx_hash,

ranked_tx_hashes.topics,

d.event_name,

TO_JSON(d.decoded_log) AS decoded_log, -- Convert OBJECT to JSON

TO_JSON(d.full_decoded_log) AS full_decoded_log, -- Convert OBJECT to JSON

ranked_tx_hashes.row_num

FROM ranked_tx_hashes

LEFT JOIN avalanche.core.fact_decoded_event_logs d

ON ranked_tx_hashes.tx_hash = d.tx_hash

SELECT

origin_function_signature,

MAX(CASE WHEN row_num = 1 THEN tx_hash ELSE NULL END) AS tx_hash_1,

MAX(CASE WHEN row_num = 1 THEN topics ELSE NULL END) AS topics_1,

MAX(CASE WHEN row_num = 1 THEN event_name ELSE NULL END) AS event_name_1,

MAX(CASE WHEN row_num = 1 THEN decoded_log ELSE NULL END) AS decoded_log_1,

MAX(CASE WHEN row_num = 1 THEN full_decoded_log ELSE NULL END) AS full_decoded_log_1,

MAX(CASE WHEN row_num = 1500 THEN tx_hash ELSE NULL END) AS tx_hash_2,

MAX(CASE WHEN row_num = 1500 THEN topics ELSE NULL END) AS topics_2,

MAX(CASE WHEN row_num = 1500 THEN event_name ELSE NULL END) AS event_name_2,

MAX(CASE WHEN row_num = 1500 THEN decoded_log ELSE NULL END) AS decoded_log_2,

MAX(CASE WHEN row_num = 1500 THEN full_decoded_log ELSE NULL END) AS full_decoded_log_2,