SELECT

tx_id,

block_timestamp,

SWAPPER,

-- Calculate Buy and Sell Quantities

CASE

WHEN SWAP_TO_MINT = 'Df6yfrKC8kZE3KNkrHERKzAetSxbrWeniQfyJY4Jpump' THEN SWAP_TO_AMOUNT

ELSE 0

END AS BUY_QUANTITY,

CASE

WHEN SWAP_FROM_MINT = 'Df6yfrKC8kZE3KNkrHERKzAetSxbrWeniQfyJY4Jpump' THEN SWAP_FROM_AMOUNT

ELSE 0

END AS SELL_QUANTITY,

-- Handle missing USD values using COALESCE

CASE

WHEN SWAP_TO_MINT = 'Df6yfrKC8kZE3KNkrHERKzAetSxbrWeniQfyJY4Jpump' THEN COALESCE(SWAP_FROM_AMOUNT_USD, SWAP_TO_AMOUNT_USD)

END AS BUY_USD,

CASE

WHEN SWAP_FROM_MINT = 'Df6yfrKC8kZE3KNkrHERKzAetSxbrWeniQfyJY4Jpump' THEN COALESCE(SWAP_TO_AMOUNT_USD, SWAP_FROM_AMOUNT_USD)

END AS SELL_USD

FROM solana.defi.ez_dex_swaps

WHERE SWAP_FROM_MINT = 'Df6yfrKC8kZE3KNkrHERKzAetSxbrWeniQfyJY4Jpump'

OR SWAP_TO_MINT = 'Df6yfrKC8kZE3KNkrHERKzAetSxbrWeniQfyJY4Jpump'

),

wallet_summary AS (

SELECT

SWAPPER,

SUM(BUY_QUANTITY) AS TOTAL_BUY_QUANTITY,

SUM(SELL_QUANTITY) AS TOTAL_SELL_QUANTITY,

SUM(BUY_USD) AS TOTAL_BUY_USD,

SUM(SELL_USD) AS TOTAL_SELL_USD,

-- Net Held Quantity (tokens not yet sold)

SUM(BUY_QUANTITY) - SUM(SELL_QUANTITY) AS NET_HELD_QUANTITY

FROM token_swaps