Finding Polymarket's Top Traders by ROI with Pure SQL

What separates winning traders from the rest on Polymarket? In this post, we’ll build a pure SQL query that identifies the top traders by ROI for a specific time period. No Python, no pandas - just ClickHouse SQL doing the heavy lifting.

Our goal: Find the traders with the highest ROI from January 10-15, 2026.

The Methodology

To calculate a trader’s true performance, we need to account for:

1. Realized PnL - Profits/losses from closed positions (tokens bought and sold)
2. Unrealized PnL - Value of remaining positions based on market resolution prices

The formula is simple:

Total PnL = (USDC received from sells - USDC spent on buys) + (remaining tokens × resolution price)
ROI % = Total PnL / Total Invested × 100

Let’s build this query step by step.

Step 1: Understanding the Data

First, let’s explore the polymarket_order_filled table to understand what we’re working with.

SELECT
    wallet,
    asset,
    side,
    amount_usdc / 1e6 as usdc_amount,
    amount_token / 1e6 as token_amount,
    block_timestamp
FROM polymarket.polymarket_order_filled
WHERE block_timestamp >= '2026-01-10'
  AND block_timestamp < '2026-01-16'
LIMIT 10

Key columns:

• wallet - The trader’s address
• asset - The market token ID (each outcome has its own token)
• side - ‘B’ for buy, ‘S’ for sell
• amount_usdc - USDC amount (6 decimals, divide by 1e6)
• amount_token - Token amount (6 decimals, divide by 1e6)

Let’s see how much activity we have in our period:

SELECT
    COUNT(*) as total_trades,
    COUNT(DISTINCT wallet) as unique_wallets,
    COUNT(DISTINCT asset) as unique_assets,
    SUM(amount_usdc) / 1e6 as total_volume_usdc
FROM polymarket.polymarket_order_filled
WHERE block_timestamp >= '2026-01-10'
  AND block_timestamp < '2026-01-16'

Step 2: Calculate Positions per Wallet-Asset

Now let’s aggregate trades by wallet and asset to see net positions. We’ll track:

• Tokens bought vs sold
• USDC spent vs received
• Net token position

WITH period_trades AS (
    SELECT
        wallet,
        asset,
        side,
        amount_usdc / 1e6 as usdc_amount,
        amount_token / 1e6 as token_amount
    FROM polymarket.polymarket_order_filled
    WHERE block_timestamp >= '2026-01-10'
      AND block_timestamp < '2026-01-16'
),
positions AS (
    SELECT
        wallet,
        asset,
        SUM(CASE WHEN side = 'B' THEN token_amount ELSE 0 END) as tokens_bought,
        SUM(CASE WHEN side = 'S' THEN token_amount ELSE 0 END) as tokens_sold,
        SUM(CASE WHEN side = 'B' THEN usdc_amount ELSE 0 END) as usdc_spent,
        SUM(CASE WHEN side = 'S' THEN usdc_amount ELSE 0 END) as usdc_received
    FROM period_trades
    GROUP BY wallet, asset
)
SELECT
    wallet,
    asset,
    tokens_bought,
    tokens_sold,
    tokens_bought - tokens_sold as net_tokens,
    usdc_spent,
    usdc_received,
    usdc_received - usdc_spent as realized_pnl
FROM positions
ORDER BY usdc_spent DESC
LIMIT 20

This shows each wallet’s position in each asset:

• tokens_bought - tokens_sold = remaining position (can be negative if short)
• usdc_received - usdc_spent = realized portion of PnL

Step 3: Get Market Resolution Prices

To value unrealized positions, we need the market resolution prices from raw_market_meta. The key is joining on clob_token_id:

WITH period_trades AS (
    SELECT
        wallet,
        asset,
        side,
        amount_usdc / 1e6 as usdc_amount,
        amount_token / 1e6 as token_amount
    FROM polymarket.polymarket_order_filled
    WHERE block_timestamp >= '2026-01-10'
      AND block_timestamp < '2026-01-16'
),
positions AS (
    SELECT
        wallet,
        asset,
        SUM(CASE WHEN side = 'B' THEN token_amount ELSE 0 END) as tokens_bought,
        SUM(CASE WHEN side = 'S' THEN token_amount ELSE 0 END) as tokens_sold,
        SUM(CASE WHEN side = 'B' THEN usdc_amount ELSE 0 END) as usdc_spent,
        SUM(CASE WHEN side = 'S' THEN usdc_amount ELSE 0 END) as usdc_received
    FROM period_trades
    GROUP BY wallet, asset
),
market_prices AS (
    SELECT DISTINCT
        clob_token_id as asset,
        toFloat64OrZero(outcome_price) as resolution_price
    FROM polymarket.raw_market_meta
    WHERE clob_token_id IN (SELECT DISTINCT asset FROM positions)
)
SELECT
    p.wallet,
    p.asset,
    p.tokens_bought - p.tokens_sold as net_tokens,
    p.usdc_spent,
    p.usdc_received,
    mp.resolution_price
FROM positions p
LEFT JOIN market_prices mp ON p.asset = mp.asset
WHERE p.usdc_spent > 100  -- Filter small positions
ORDER BY p.usdc_spent DESC
LIMIT 20

The LEFT JOIN ensures we keep positions even if we don’t have a resolution price (we’ll default those to 0).

Step 4: Calculate Position-Level PnL

Now we can calculate both realized and unrealized PnL for each position:

WITH period_trades AS (
    SELECT
        wallet,
        asset,
        side,
        amount_usdc / 1e6 as usdc_amount,
        amount_token / 1e6 as token_amount
    FROM polymarket.polymarket_order_filled
    WHERE block_timestamp >= '2026-01-10'
      AND block_timestamp < '2026-01-16'
),
positions AS (
    SELECT
        wallet,
        asset,
        SUM(CASE WHEN side = 'B' THEN token_amount ELSE 0 END) as tokens_bought,
        SUM(CASE WHEN side = 'S' THEN token_amount ELSE 0 END) as tokens_sold,
        SUM(CASE WHEN side = 'B' THEN usdc_amount ELSE 0 END) as usdc_spent,
        SUM(CASE WHEN side = 'S' THEN usdc_amount ELSE 0 END) as usdc_received
    FROM period_trades
    GROUP BY wallet, asset
),
market_prices AS (
    SELECT DISTINCT
        clob_token_id as asset,
        toFloat64OrZero(outcome_price) as resolution_price
    FROM polymarket.raw_market_meta
    WHERE clob_token_id IN (SELECT DISTINCT asset FROM positions)
),
position_pnl AS (
    SELECT
        p.wallet,
        p.asset,
        p.tokens_bought,
        p.tokens_sold,
        p.tokens_bought - p.tokens_sold as net_tokens,
        p.usdc_spent,
        p.usdc_received,
        COALESCE(mp.resolution_price, 0) as resolution_price,
        -- Realized PnL: what you got back minus what you spent
        p.usdc_received - p.usdc_spent as cash_flow,
        -- Unrealized PnL: remaining tokens valued at resolution price
        (p.tokens_bought - p.tokens_sold) * COALESCE(mp.resolution_price, 0) as unrealized_value,
        -- Total PnL
        (p.usdc_received - p.usdc_spent) +
        ((p.tokens_bought - p.tokens_sold) * COALESCE(mp.resolution_price, 0)) as total_pnl
    FROM positions p
    LEFT JOIN market_prices mp ON p.asset = mp.asset
)
SELECT
    wallet,
    asset,
    round(usdc_spent, 2) as invested,
    round(usdc_received, 2) as received,
    round(net_tokens, 2) as remaining_tokens,
    round(resolution_price, 4) as resolution_price,
    round(unrealized_value, 2) as unrealized_value,
    round(total_pnl, 2) as total_pnl
FROM position_pnl
WHERE usdc_spent > 100
ORDER BY total_pnl DESC
LIMIT 20

Step 5: Aggregate to Wallet Level

Time to sum up all positions per wallet and calculate overall ROI:

WITH period_trades AS (
    SELECT
        wallet,
        asset,
        side,
        amount_usdc / 1e6 as usdc_amount,
        amount_token / 1e6 as token_amount
    FROM polymarket.polymarket_order_filled
    WHERE block_timestamp >= '2026-01-10'
      AND block_timestamp < '2026-01-16'
),
positions AS (
    SELECT
        wallet,
        asset,
        SUM(CASE WHEN side = 'B' THEN token_amount ELSE 0 END) as tokens_bought,
        SUM(CASE WHEN side = 'S' THEN token_amount ELSE 0 END) as tokens_sold,
        SUM(CASE WHEN side = 'B' THEN usdc_amount ELSE 0 END) as usdc_spent,
        SUM(CASE WHEN side = 'S' THEN usdc_amount ELSE 0 END) as usdc_received
    FROM period_trades
    GROUP BY wallet, asset
),
market_prices AS (
    SELECT DISTINCT
        clob_token_id as asset,
        toFloat64OrZero(outcome_price) as resolution_price
    FROM polymarket.raw_market_meta
    WHERE clob_token_id IN (SELECT DISTINCT asset FROM positions)
),
position_pnl AS (
    SELECT
        p.wallet,
        p.asset,
        p.usdc_spent,
        p.usdc_received,
        p.tokens_bought - p.tokens_sold as net_tokens,
        COALESCE(mp.resolution_price, 0) as resolution_price,
        (p.usdc_received - p.usdc_spent) +
        ((p.tokens_bought - p.tokens_sold) * COALESCE(mp.resolution_price, 0)) as total_pnl
    FROM positions p
    LEFT JOIN market_prices mp ON p.asset = mp.asset
)
SELECT
    wallet,
    COUNT(DISTINCT asset) as num_positions,
    round(SUM(usdc_spent), 2) as total_invested,
    round(SUM(usdc_received), 2) as total_received,
    round(SUM(net_tokens * resolution_price), 2) as unrealized_value,
    round(SUM(total_pnl), 2) as total_pnl
FROM position_pnl
GROUP BY wallet
HAVING SUM(usdc_spent) > 1000  -- Minimum $1000 invested
ORDER BY total_pnl DESC
LIMIT 20

Step 6: The Final Query - Top Traders by ROI

Here’s the complete query that ranks traders by ROI percentage:

WITH period_trades AS (
    -- Extract trades from our analysis period
    SELECT
        wallet,
        asset,
        side,
        amount_usdc / 1e6 as usdc_amount,
        amount_token / 1e6 as token_amount
    FROM polymarket.polymarket_order_filled
    WHERE block_timestamp >= '2026-01-10'
      AND block_timestamp < '2026-01-16'
),
positions AS (
    -- Aggregate to wallet-asset level
    SELECT
        wallet,
        asset,
        SUM(CASE WHEN side = 'B' THEN token_amount ELSE 0 END) as tokens_bought,
        SUM(CASE WHEN side = 'S' THEN token_amount ELSE 0 END) as tokens_sold,
        SUM(CASE WHEN side = 'B' THEN usdc_amount ELSE 0 END) as usdc_spent,
        SUM(CASE WHEN side = 'S' THEN usdc_amount ELSE 0 END) as usdc_received
    FROM period_trades
    GROUP BY wallet, asset
),
market_prices AS (
    -- Get resolution prices for traded assets
    SELECT DISTINCT
        clob_token_id as asset,
        toFloat64OrZero(outcome_price) as resolution_price
    FROM polymarket.raw_market_meta
    WHERE clob_token_id IN (SELECT DISTINCT asset FROM positions)
),
position_pnl AS (
    -- Calculate PnL per position
    SELECT
        p.wallet,
        p.asset,
        p.usdc_spent,
        p.usdc_received,
        p.tokens_bought - p.tokens_sold as net_tokens,
        COALESCE(mp.resolution_price, 0) as resolution_price,
        (p.usdc_received - p.usdc_spent) +
        ((p.tokens_bought - p.tokens_sold) * COALESCE(mp.resolution_price, 0)) as total_pnl
    FROM positions p
    LEFT JOIN market_prices mp ON p.asset = mp.asset
),
wallet_stats AS (
    -- Aggregate to wallet level
    SELECT
        wallet,
        COUNT(DISTINCT asset) as num_positions,
        SUM(usdc_spent) as total_invested,
        SUM(usdc_received) as total_received,
        SUM(net_tokens * resolution_price) as unrealized_value,
        SUM(total_pnl) as total_pnl
    FROM position_pnl
    GROUP BY wallet
    HAVING SUM(usdc_spent) > 1000  -- Minimum $1000 invested to filter noise
)
SELECT
    wallet,
    num_positions,
    round(total_invested, 2) as total_invested,
    round(total_received, 2) as total_received,
    round(unrealized_value, 2) as unrealized_value,
    round(total_pnl, 2) as total_pnl,
    round((total_pnl / total_invested) * 100, 2) as roi_pct
FROM wallet_stats
WHERE total_invested > 0
ORDER BY roi_pct DESC
LIMIT 50

Understanding the Results

This query returns the top 50 traders ranked by ROI percentage. Each row shows:

Column	Description
wallet	Trader’s address
num_positions	Number of unique markets traded
total_invested	Total USDC spent on buys
total_received	Total USDC received from sells
unrealized_value	Value of remaining positions
total_pnl	Total profit/loss
roi_pct	Return on investment percentage

Key Observations

1. High ROI isn’t always high volume - Some of the best ROI traders have modest investment sizes but picked winning markets consistently.

2. Diversification varies - Top performers range from concentrated bets (1-2 positions) to diversified portfolios (10+ positions).

3. Resolution price matters - Traders with open positions in unresolved markets may have their PnL undervalued (priced at 0).

Edge Cases and Limitations

1. Unresolved markets: If a market hasn’t resolved, outcome_price may be NULL or stale. We default to 0, which understates unrealized value.

2. Multiple outcome tokens: Binary markets have “Yes” and “No” tokens. This query treats each token independently.

3. Time boundary effects: Positions opened before Jan 10 but closed during the period aren’t fully captured.

4. Fees not included: The amount_usdc includes fees paid, which slightly overstates costs.

Adapting for Other Periods

To analyze a different time period, simply modify the date filters:

-- Change these dates in the period_trades CTE
WHERE block_timestamp >= '2026-01-01'  -- Start date
  AND block_timestamp < '2026-01-08'   -- End date (exclusive)

You can also adjust the minimum investment threshold in the HAVING clause to filter for larger or smaller traders.

Conclusion

With a single SQL query, we’ve built a complete PnL and ROI calculation for Polymarket traders. The iterative approach - starting with raw data exploration and building up to the final aggregation - makes the logic transparent and easy to modify.

Key takeaways:

1. CTEs are your friend - Breaking complex logic into named steps makes queries readable and debuggable
2. COALESCE handles edge cases - Default NULL prices to 0 to avoid calculation errors
3. Aggregate in the database - ClickHouse can handle millions of trades; let it do the heavy lifting
4. ROI > absolute PnL - Ranking by percentage returns reveals skill regardless of capital size

This same pattern can be extended to analyze specific markets, time-weighted returns, or even identify market makers vs directional traders.

---

The queries in this post were run against ClickHouse. Adapt the syntax if using a different database.