regexp_replace(f_logs.value, '^Program log: Instruction: ') as instruction_log

, fact_events.program_id

-- , substr(livequery.utils.udf_base58_to_hex(instruction :data), 1, 18) as data_18

, tx_id

-- , instruction :data

-- , count(distinct tx_id)

from solana.core.fact_events

inner join solana.core.fact_transactions

using(tx_id, block_timestamp, succeeded, block_id)

inner join lateral flatten (input => fact_transactions.log_messages) f_logs

where succeeded

-- and (program_id in ('CCTPmbSD7gX1bxKPAmg77w8oFzNFpaQiQUWD43TKaecd','CCTPiPYPc6AsJuwueEnWgSgucamXDZwBd53dQ11YiKX3')

-- or array_contains('CCTPmbSD7gX1bxKPAmg77w8oFzNFpaQiQUWD43TKaecd' ::variant, instruction :accounts)

-- or array_contains('CCTPiPYPc6AsJuwueEnWgSgucamXDZwBd53dQ11YiKX3' ::variant, instruction :accounts))

and program_id = 'CoREENxT6tW1HoK8ypY1SxRMZTcVPm7R94rH4PZNhX7d'

and fact_transactions.log_messages[f_logs.index -1] like 'Program CoREENxT6tW1HoK8ypY1SxRMZTcVPm7R94rH4PZNhX7d invoke [%]'

-- or fact_transactions.log_messages[f_logs.index -1] like 'Program CCTPiPYPc6AsJuwueEnWgSgucamXDZwBd53dQ11YiKX3 invoke [%]')

and f_logs.value like 'Program log: Instruction: %'

-- and block_timestamp > '2024-03-25'

-- and block_timestamp < '2023-12-14'

and block_timestamp > current_date() - 7

qualify row_number() over (partition by instruction_log order by block_timestamp desc) <= 1

order by program_id, instruction_log

limit 100