-- forked from Elixir - [activity] volume and trades @ https://flipsidecrypto.xyz/edit/queries/86146c87-28e6-47af-9db6-ae7cc45b51b2

activity as (

select distinct

txs.block_timestamp,

txs.tx_id,

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

from solana.core.fact_transactions txs

inner join lateral flatten (input => log_messages) f_logs

inner join (

select distinct block_timestamp, tx_id

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'

),