SELECT

tx_id,

signers[0] as signer,

block_timestamp,

fee,

value::STRING AS log_message

FROM solana.core.fact_transactions,

LATERAL FLATTEN(input => log_messages)

WHERE 1=1

AND succeeded = TRUE

-- AND tx_id = ''

-- AND signers[0] in ('')

AND block_timestamp >= '2024-08-06'

AND value::STRING LIKE '%Program return: oreV2ZymfyeXgNgBdqMkumTqqAprVqgBWQfoYkrtKWQ%'

), extracted_logs AS (

SELECT

tx_id,

signer,

block_timestamp,

fee,

REGEXP_SUBSTR(

log_message,

'Program return: oreV2ZymfyeXgNgBdqMkumTqqAprVqgBWQfoYkrtKWQ\\s+([A-Za-z0-9+/=]+)',

1,

1,

'e'

) AS base64_data

FROM txns

), decoded_logs AS (

SELECT

tx_id,

signer,

block_timestamp,

fee,

base64_data,