WITH swap_data AS (

e.block_timestamp,

ft.tx_id,

t.index,

t.inner_index,

t.mint,

t.amount / pow(10, tokens.decimal) AS swap_amount,

tokens.symbol,

ft.signers[0] AS swapper,

CASE WHEN t.inner_index = 0 THEN 'Outflow' ELSE 'Inflow' END AS type

eclipse.core.fact_events e

eclipse.core.fact_transactions ft ON e.tx_id = ft.tx_id

eclipse.core.fact_transfers t ON e.tx_id = t.tx_id

LEFT JOIN (

SELECT 'USDC' AS symbol, 'USD Coin' AS name, 'AKEWE7Bgh87GPp171b4cJPSSZfmZwQ3KaqYqXoKLNAEE' AS mint, 6 AS decimal

UNION ALL

SELECT 'SOL', 'Solana', 'BeRUj3h7BqkbdfFU7FBNYbodgf8GCHodzKvF9aVjNNfL', 9

UNION ALL

SELECT 'WIF', 'DogWifHat', '841P4tebEgNux2jaWSjCoi9LhrVr9eHGjLc758Va3RPH', 9

UNION ALL

SELECT 'ETH', 'Ethereum', 'So11111111111111111111111111111111111111112', 9) tokens ON t.mint = tokens.mint

WHERE e.program_id = '4UsSbJQZJTfZDFrgvcPBRCSg5BbcQE6dobnriCafzj12'

),

swap_aggregated AS (

block_timestamp,

swapper,

tx_id,

SUM(CASE WHEN type = 'Outflow' THEN swap_amount ELSE 0 END) AS outflow,

SUM(CASE WHEN type = 'Inflow' THEN swap_amount ELSE 0 END) AS inflow

FROM swap_data