select

tx_id,

signers[0] as signer,

block_timestamp,

block_id,

tx_index

from solana.core.fact_transactions

where

(

('{{n_days}}' = 0 and block_timestamp between cast('{{start_date}}' as timestamp) and cast('{{end_date}}' as timestamp))

or ('{{n_days}}' != 0 and block_timestamp >= current_date() - interval '{{n_days}} days')

)

,swap_data as (

select

tr.tx_id,

-- Assumiamo che in fact_transfers siano disponibili o calcolabili i campi relativi allo swap:

tr.outer_program,

tr.inner_program,

tr.amm,

tr.subject,

tr.input_amount,

tr.input_mint,

tr.output_amount,

tr.output_mint,

-- Qui definiamo il tipo di swap in modo semplice; potresti adattare la logica se hai regole diverse

case

when t.signers[0] = tr.tx_from then 'FRONTRUN'

when t.signers[0] = tr.tx_to then 'BACKRUN'

else 'VICTIM'

end as swap_type

from solana.core.fact_transfers tr

join solana.core.fact_transactions t on t.tx_id = tr.tx_id