Practice — Advanced SQL: Window Functions & CTEs (5 questions)

You're asked to write a report: "the top 3 best-selling products per category, by units sold." You write:

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

The report ships. A week later, a merchandising analyst complains: "Two products in the 'Outdoor' category sold the exact same number of units for the #3 spot, and your report only shows one of them — the one that happens to show up depends on which day I re-run the query."

1. Diagnose exactly why the same query returns a different product for the tied spot on different runs.
2. Fix the query to make it deterministic. Is a deterministic result alone enough to satisfy the analyst's actual complaint?
3. The analyst also asks: "actually, I want every product tied for #1/#2/#3, even if that means showing 4 or 5 products in a category that has ties." Rewrite the query to satisfy this new requirement, and explain why your original approach structurally cannot.