with

activity as (

select distinct

txs.block_timestamp,

txs.tx_id,

txs.signers[0] as user_address,

max(transfers.amount) over (partition by txs.tx_id) as amount,

regexp_replace(txs.log_messages[f_logs.index + 1], '^Program log: Instruction: (Orca)?') as label_action,

elixir_txs.mint

from solana.core.fact_transactions txs

inner join lateral flatten (input => log_messages) f_logs

inner join (

select distinct block_timestamp, tx_id, instruction :accounts[1] ::string as mint

from solana.core.fact_events

where succeeded

and program_id = 'E1XRkj9fPF2NQUdoq41AHPqwMDHykYfn5PzBXAyDs7Be'

and block_timestamp ::date > current_date() - interval '{{months}} months'

) elixir_txs

using(tx_id, block_timestamp)

inner join solana.core.fact_transfers transfers

using(tx_id, block_timestamp)

where f_logs.value like 'Program E1XRkj9fPF2NQUdoq41AHPqwMDHykYfn5PzBXAyDs7Be invoke%'

and regexp_replace(txs.log_messages[f_logs.index + 1], '^Program log: Instruction: (Orca)?') in ('Buy', 'Sell')

and case when regexp_replace(txs.log_messages[f_logs.index + 1], '^Program log: Instruction: (Orca)?') = 'Sell'

then not transfers.tx_from = txs.signers[0]

else not transfers.tx_from is null end

and transfers.mint = 'So11111111111111111111111111111111111111112'

and block_timestamp ::date > current_date() - interval '{{months}} months'