WITH Market_tags AS (SELECT
regexp_substr_all( SUBSTR(DATA, 3, len(DATA)), '.{64}' ) AS segmented_data,
concat('0x',SUBSTR(segmented_data[0], 25, 40)) AS market,
ethereum.public.udf_hex_to_int(concat('0x',SUBSTR(segmented_data[17], 25, 40))) AS Tags1,
ethereum.public.udf_hex_to_int(concat('0x',SUBSTR(segmented_data[18], 25, 40))) AS Tags2,
ethereum.public.udf_hex_to_int(concat('0x',SUBSTR(segmented_data[19], 25, 40))) AS Tags3,
CASE
WHEN Tags1 >= 9000 and Tags1 <=9020 THEN Tags1
WHEN Tags2 >= 9000 and Tags2 <=9020 THEN Tags2
WHEN Tags3 >= 9000 and Tags3 <=9020 THEN Tags3
END AS TAG,
*
FROM optimism.core.fact_event_logs
WHERE ORIGIN_FROM_ADDRESS='0x646f0733614cfcaffc72f6147228c72bc782d452'
AND ORIGIN_TO_ADDRESS='0x2b91c14ce9aa828ed124d12541452a017d8a2148'
AND TOPICS[0]::string='0x889e2060e46779287c2fcbf489c195ef20f5b44a74e3dcb58d491ae073c1370f'
AND TX_STATUS='SUCCESS'
)
SELECT MARKET,TAG,BLOCK_TIMESTAMP, TX_HASH
FROM Market_tags