Top Two Products per Category with Ties
A sales analytics table has 22 million rows: CREATE TABLE sales (id BIGINT PRIMARY KEY, category_id INT, product_id INT, sold_at DATETIME, revenue DECIMAL(12,2), KEY idx_sold (sold_at));. The business wants the top two products per category by total revenue for the last 90 days. If products tie at the cutoff, the report should include all of them. The current PHP code runs SELECT category_id, product_id, SUM(revenue) AS total FROM sales WHERE sold_at >= '2024-01-01' GROUP BY category_id, product_id ORDER BY category_id, total DESC;, fetches every category/product row, and filters in PHP. It now takes over 90 seconds and sometimes exhausts memory. The team wants a single SQL statement that returns only the report rows and behaves predictably with ties.
1. Write a CTE-based query using a window function to return the top two revenue levels per category. Explain why the window filter cannot be in the same SELECT as the window function.
Read the worked answer
WITH totals AS (
SELECT category_id, product_id, SUM(revenue) AS total
FROM sales
WHERE sold_at >= '2024-01-01'
AND sold_at < '2024-04-01'
GROUP BY category_id, product_id
), ranked AS (
SELECT category_id, product_id, total,
DENSE_RANK() OVER (
PARTITION BY category_id
ORDER BY total DESC, product_id
) AS rnk
FROM totals
)
SELECT category_id, product_id, total, rnk
FROM ranked
WHERE rnk <= 2
ORDER BY category_id, rnk, product_id;
Window functions are evaluated after the WHERE, GROUP BY, and HAVING phases. A predicate on DENSE_RANK() cannot be applied in the same query block because the rank does not exist yet at that point. The CTE materializes the aggregates and ranks first; the outer query filters the ranked result. The product_id tie-breaker makes the ordering deterministic, though DENSE_RANK() still gives tied revenue rows the same rank.
2. Compare `ROW_NUMBER()`, `RANK()`, and `DENSE_RANK()` for this requirement. When would each produce a different report?
Read the worked answer
ROW_NUMBER() assigns 1, 2, 3, ... even when revenue values are equal. If two products tie for second place, one gets 2 and the other gets 3, so filtering rn <= 2 arbitrarily drops one of the tied products. That violates the requirement to include ties.
RANK() assigns the same rank to ties but leaves gaps: 1, 1, 3. If three products tie for first, all get rank 1 and the next revenue level gets rank 4, so rn <= 2 returns the three tied leaders and no second revenue level. This matches a rule of "at most two rank positions, with ties expanding the first position."
DENSE_RANK() assigns 1, 1, 2 without gaps. If three products tie for first, all get rank 1, and the next distinct revenue level gets rank 2, so rn <= 2 returns four products. This matches a rule of "the top two distinct revenue levels, including every product at those levels."
The requirement says to include all products tied at the cutoff, so DENSE_RANK() is the safest default when "top two" means two revenue levels. If the business means two positions, use RANK() and document that a large tie at first place can consume the report.
3. The existing index is only on `sold_at`. What indexing and query-shape changes would you evaluate to reduce the 90-second runtime?
Read the worked answer
The existing idx_sold(sold_at) can range-scan the 90-day window, but it is not covering: MySQL must fetch each row to get category_id, product_id, and revenue. A covering index such as (sold_at, category_id, product_id, revenue) lets the range scan read all needed columns from the index. The trade-off is a wider index and more write cost.
Because the query filters only on sold_at and aggregates across all categories, a leading sold_at column is appropriate. An index beginning with category_id would help a single-category report but not the global report. Even with a covering index, the GROUP BY category_id, product_id still needs an aggregation step, and the window function needs a sort or hash. Run EXPLAIN ANALYZE to see whether the bottleneck is the range scan, the temporary table, or the window sort.
For a recurring 90-day report over 22 million rows, consider a summary table keyed by (sold_at_date, category_id, product_id) with daily revenue, refreshed incrementally. The window query can then run on a much smaller aggregate. Do not add DATE(sold_at) predicates; keep the range on the raw column so the index remains usable.