Top 3 Products Per Category, But the Numbers Look Wrong

1. Why the result is non-deterministic

ROW_NUMBER() guarantees a unique, sequential integer per row within the partition, but when two rows are tied on the ORDER BY key (units_sold), it has to break the tie somehow to assign distinct numbers — and the SQL standard does not define which tied row gets which number. In practice, the tie is broken by whatever incidental order the engine happens to read the rows in (physical storage order, which page a row lives on, parallel worker scan order, etc.), which can differ between runs, especially after the table has been updated, vacuumed, or replanned. The query isn't buggy in the sense of producing wrong syntax or an error — it's under-specified: nothing in the query tells the engine how to break the tie, so it's free to do so arbitrarily and inconsistently.

2. Making it deterministic — and why that's not the whole fix

Add a tiebreaker column to ORDER BY inside the window clause so every row has a fully-specified sort order:

ROW_NUMBER() OVER (
  PARTITION BY category_id
  ORDER BY units_sold DESC, product_id ASC   -- tiebreaker
) AS rn

This makes the query reproducible — the same input always produces the same output — which is a real and necessary fix (a report that changes answers on identical data with no data change is a trust problem regardless of what the "correct" tied product is). But it is not enough to satisfy the analyst's actual complaint: determinism just picks a consistent arbitrary winner (in this case, always the lower product_id among ties) — it doesn't change the fact that one of the two tied products is still being silently dropped from the top-3 list every single run. The analyst's underlying ask, once you read between the lines, is about which product appears, not just about the answer being stable.

3. Including all ties — a structurally different function

WITH ranked AS (
  SELECT
    product_id,
    category_id,
    units_sold,
    RANK() OVER (
      PARTITION BY category_id
      ORDER BY units_sold DESC
    ) AS rnk
  FROM product_sales
)
SELECT product_id, category_id, units_sold
FROM ranked
WHERE rnk <= 3
ORDER BY category_id, rnk;

RANK() gives every tied row the same rank and skips the ranks a tie consumes (two products tied at rank 3 mean the next distinct value is rank 5, not 4) — so WHERE rnk <= 3 naturally includes both products tied for 3rd place, which is exactly what "everyone tied for #1/#2/#3" means. ROW_NUMBER() cannot express this at all, and not because of a missing tiebreaker: it is defined to assign a strictly unique integer per row within the partition, so it structurally cannot give two rows the same number — using it for "include all ties" isn't a bug to fix with better ORDER BY, it's the wrong function for the requirement, full stop. This is the single most important judgment call in the top-N-per-group pattern: ROW_NUMBER() answers "give me exactly N rows," RANK() answers "give me everyone in the top N tiers, even if that's more than N rows" — and a requirements conversation, not a syntax choice, is what determines which one is correct.