Calculating Realized PnL from DEX Trades Using FIFO in SQL

A walkthrough of a pure-SQL approach to cost basis tracking across all DEX swaps.

Every DEX swap is publicly recorded on-chain. But knowing what someone traded is different from knowing whether they profited. To answer the latter, you need to match buys against sells and account for cost basis — and do it correctly across wallets that may have bought the same token multiple times at different prices.

This query does exactly that, using a technique borrowed from traditional finance: FIFO (First In, First Out) cost basis matching, implemented entirely in SQL using window functions.

FIFO means the first tokens you bought are the first ones attributed to a sale. This is the standard method for realized PnL calculation in tax accounting — and it maps cleanly to on-chain data because every trade is timestamped to its block.

What this query covers

The query pulls all DEX trades from dex.trades for April 2026 from Dune Analytics, scoped to swaps involving a defined set of stablecoins (USDT, USDC, DAI, FDUSD, USDD, FRAX, TUSD). Any swap where a wallet pays stables to receive a token counts as a buy. Any swap where a wallet gives up a token to receive stables counts as a sell.

No wallet filter is applied — this runs across all wallets present in the trades table for that period. The LIMIT 30 at the end is a sanity cap for exploration.

1️⃣ BUY_ORDER — All swaps where the wallet spent a stablecoin and received a non-stable token. This is the cost basis side.

2️⃣ SELL_ORDER — All swaps where the wallet spent a non-stable token and received a stablecoin. This is the exit side.

3️⃣ CUMULATIVE_TOTAL_BUY — Running cumulative sum of tokens bought per wallet per token address, ordered by block time. This turns individual trades into a positional queue.

4️⃣ CUMULATIVE_TOTAL_SELL — Same thing for sells.

5️⃣ FIFO_MATCHING — Joins the two cumulative CTEs and finds overlapping segments. Overlapping ranges represent matched buy-sell pairs.

Step 1 & 2: Separating buys from sells

The logic is symmetric. A buy event is when token_sold_symbol is a stablecoin and token_bought_symbol is not. A sell event is the inverse.

-- BUY: paid stable, got token
WHERE token_sold_symbol IN ('USDT', 'USDC', 'DAI', 'FDUSD', ...)
  AND token_bought_symbol NOT IN ('USDT', 'USDC', ...)

-- SELL: gave token, got stable
WHERE token_bought_symbol IN ('USDT', 'USDC', 'DAI', 'FDUSD', ...)
  AND token_sold_symbol NOT IN ('USDT', 'USDC', ...)

Both CTEs pull amount_usd, which Dune pre-calculates. We'll use this to derive per-token price in the matching step.

Step 3 & 4: Building the FIFO queue

The key insight: if you compute a running sum of token amounts, each row implicitly represents a range on the number line. A wallet that bought 100 tokens, then 200 more, has rows covering [0 → 100] and [100 → 300]. This is the queue.

SUM(token_bought_amount) OVER (
    PARTITION BY tx_from, token_bought_address
    ORDER BY block_time ASC
) AS cumulative_total_token_bought

The start of a row’s range is cumulative - amount. The end is cumulative. Same math applies to the sell side.

Step 5: FIFO matching via range overlap

This is the most interesting part. We join buys and sells on tx_from and token_address, then add two overlap conditions that test whether the buy range and sell range intersect:

INNER JOIN CUMULATIVE_TOTAL_SELL s
    ON b.tx_from = s.tx_from
    AND b.token_bought_address = s.token_sold_address
    -- buy range doesn't end before sell range starts
    AND (b.cumulative_total_token_bought - b.token_bought_amount) 
            < s.cumulative_total_token_sold
    -- buy range doesn't start after sell range ends
    AND b.cumulative_total_token_bought 
            > (s.cumulative_total_token_sold - s.token_sold_amount)

Every row that passes this join is a valid buy-sell pair with some overlapping token quantity. The overlap is the matched amount — computed via two CASE WHEN statements that find the higher of the two range starts, and the lower of the two range ends:

CASE
    WHEN (b.cumulative_total_token_bought - b.token_bought_amount) > (s.cumulative_total_token_sold - s.token_sold_amount) 
    THEN (b.cumulative_total_token_bought - b.token_bought_amount)
    ELSE (s.cumulative_total_token_sold - s.token_sold_amount)
END AS start_bound,
CASE
    WHEN b.cumulative_total_token_bought < s.cumulative_total_token_sold 
    THEN b.cumulative_total_token_bought
    ELSE s.cumulative_total_token_sold
END AS end_bound

Final SELECT: realized PnL

SELECT
    tx_from,
    token_address,
    (end_bound - start_bound) AS matched_token_amount,
    (end_bound - start_bound) * (sell_price_usd_per_token - buy_price_usd_per_token) 
        AS realized_pnl_usd
FROM FIFO_MATCHING
WHERE (end_bound - start_bound) > 0
LIMIT 30

Per-token prices are derived inline: amount_usd / NULLIF(token_amount, 0). The NULLIF prevents division by zero for dust trades. PnL = matched quantity × price delta. Positive means profit, negative means loss.

The WHERE (end_bound - start_bound) > 0 removes point-touches — cases where the ranges meet at exactly one edge but have no actual overlapping volume.

Limitations

• Same-block ordering — multiple trades in the same block have non-deterministic order. Add tx_index as a tiebreaker in the window ORDER BY for stricter FIFO.
• Stablecoin-only pairs — token-to-token swaps (e.g. WETH → ARB) are excluded. You’d need a separate pass or a price oracle join to handle those.
• No unrealized PnL — tokens still held by a wallet after the period aren’t valued. Only fully closed positions appear.
• BLOCK_MONTH scope — hardcoded to April 2026. Parameterize with block_time >= DATE '...' for rolling windows.
• LIMIT 30 — fine for exploration, remove for production aggregations.
• Truncated Historical Inventory — Limiting the dataset scope strictly to April 2026 assumes all wallets start with a clean sheet. In reality, if a wallet holds tokens acquired in previous months and liquidates them in April, the engine won’t find the original buy-side rows to match the cost basis.

The full query is available on Dune — fork it and swap in your own wallet addresses or date range.

👉 https://dune.com/queries/7613138

Where to take this next

With per-row realized PnL as a base, you can aggregate to wallet-level summaries: total PnL, win rate, average hold time between buy and sell timestamps, most profitable tokens. This also composes cleanly into a smart money dashboard — identify wallets with consistently positive realized PnL, then use their buy events as a signal layer.