select b.block_timestamp,

b.tx_id,

b.index,

b.tx_from,

b.tx_to,

case when tx_from = signers[0] then 'Outflow' else 'Inflow' end as type,

amount,

inflow_swap,

mint

from solana.core.fact_transactions

join lateral flatten (input => log_messages) logs

join (select distinct block_timestamp, tx_id, instruction

from solana.core.fact_events where succeeded = True

and block_timestamp >= '2024-07-01' and block_timestamp < '2024-10-01'

-- and block_timestamp >= '2023-01-02'

and program_id = 'dst5MGcFPoBeREFAA5E3tU5ij8m5uVYwkzkSAbsLbNo'

) a using(tx_id, block_timestamp)

join (select tx_id, swap_to_amount as inflow_swap

from solana.defi.fact_swaps_jupiter_summary

where swap_to_mint = 'So11111111111111111111111111111111111111112' and swap_from_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v'

) using (tx_id)

join solana.core.fact_transfers b using (tx_id, block_timestamp)

where 1=1

and block_timestamp >= '2024-07-01' and block_timestamp < '2024-10-01'

-- and block_timestamp >= '2023-01-02'

and logs.value = 'Program log: Instruction: FulfillOrder'

and mint in ('So11111111111111111111111111111111111111112', 'So11111111111111111111111111111111111111111')

and tx_from != tx_to

order by index

, base_agg as (

SELECT

block_timestamp,

tx_id,

MAX(CASE WHEN type = 'Outflow' then tx_from else null end) as from_address,