jito_tip_txs as (

select

tx_id as jito_tx_id

, (post_balances[f_accs.index] - pre_balances[f_accs.index]) / pow(10, 9) as tip_jito

from solana.core.fact_events

inner join solana.core.fact_transactions

using(block_timestamp, tx_id, succeeded)

inner join lateral flatten (input => account_keys) f_accs

where succeeded

and fact_events.program_id = 'E6YoRP3adE5XYneSseLee15wJshDxCsmyD2WtLvAmfLi'

and substr(livequery.utils.udf_base58_to_hex(fact_events.instruction :data), 3, 16) = '9c23c76ff5fe2635'

and fact_events.instruction :accounts[3] in ( -- Jito Tip Payment Addresses https://jito-foundation.gitbook.io/mev/mev-payment-and-distribution/on-chain-addresses#mainnet

'96gYZGLnJYVFmbjzopPSU6QiEV5fGqZNyN9nmNhvrZU5'

, 'HFqU5x63VTqvQss8hp11i4wVV8bD44PvwucfZ2bU7gRe'

, 'Cw8CFyM9FkoMi7K7Crf6HNQqf4uEMzpKw6QNghXLvLkY'

, 'ADaUMid9yfUytqMBgopwjb2DTLSokTSzL1zt6iGPaS49'

, 'DfXygSm4jCyNCybVYYK6DwvWqjKee8pbDmJGcLWNDXjh'

, 'ADuUkR4vqLUMWXxW9gh6D6L8pMSawimctcNZ5pGwDcEt'

, 'DttWaMuVvTiduZRnguLF7jNxTgiMBZ1hyAumKUiL2KRL'

, '3AVi9Tg9Uo68tJfuvoKvqKNWKkC5wPdSSdeBnizKZ6jT'

)

and fact_events.instruction :accounts[3] = f_accs.value :pubkey

and block_timestamp > current_timestamp() - interval '30 days'

union all

select

tx_id as jito_tx_id