select TX_ID, VALUE:owner AS owner, value:uiTokenAmount:uiAmountString::FLOAT AS pre, VALUE:mint AS symbol

from solana.core.fact_transactions, table (flatten (input=> pre_token_balances))

where INSTRUCTIONS[1]:programId = 'SWiMDJYFUGj6cPrQ6QYYYWZtvXQdRChSVAygDZDsCHC'

AND SIGNERS[0]= VALUE:owner),

post AS(

select TX_ID, VALUE:owner AS owner, value:uiTokenAmount:uiAmountString::FLOAT AS post, VALUE:mint AS symbol

from solana.core.fact_transactions, table (flatten (input=> post_token_balances))

where INSTRUCTIONS[1]:programId = 'SWiMDJYFUGj6cPrQ6QYYYWZtvXQdRChSVAygDZDsCHC'

AND SIGNERS[0]= VALUE:owner),

tx AS(

SELECT pre.tx_id, pre.owner, pre, post, post-pre AS change, pre.symbol,

CASE

WHEN pre.symbol='5RpUwQ8wtdPCZHhu6MERp2RGrpobsbZ6MH5dDHkUjs2' THEN 'BUSD Token (Portal from BSC) (BUSDbs)'

WHEN pre.symbol='EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' THEN 'USD Coin (USDC)'

WHEN pre.symbol='Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB' THEN 'USDT (USDT)'

WHEN pre.symbol='Dn4noZ5jgGfkntzcQSUZ8czkreiZ1ForXYoV2H8Dm7S1' THEN 'Tether USD (Portal from Ethereum) (USDTet)'

WHEN pre.symbol='A9mUU4qviSctJVPJdBJWkb28deg915LYJKrzQ19ji3FM' THEN 'USD Coin (Portal from Ethereum) (USDCet)'

WHEN pre.symbol='BJUH9GJLaMSLV1E7B3SQLCy9eCfyr6zsrwGcpS2MkqR1' THEN 'Swim Hexapool LP (swimUSD)'

WHEN pre.symbol='8qJSyQprMC57TWKaYEmetUR3UUiTP2M3hXdcvFhkZdmv' THEN 'Tether USD (Portal from BSC) (USDTbs)'

END AS token_name,

CASE

WHEN change<0 THEN 'in'

WHEN change>0 THEN 'out'

END AS in_out

FROM pre JOIN post ON pre.TX_ID=post.TX_ID AND pre.symbol=post.symbol

WHERE change!=0)

SELECT round(sum(change),0)*-1 AS volume, token_name, in_out

FROM tx

GROUP BY 3,2

ORDER BY 3,1