Write a query to find the second most frequently ordered item for each customer.

Answer

Count how many times each customer ordered each item, rank those items per customer, and pick the item ranked second.

Solution

SELECT customer_id, item_id, order_count
FROM (
  SELECT customer_id, item_id, COUNT(*) AS order_count,
         DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY COUNT(*) DESC) AS rnk
  FROM OrderItems
  GROUP BY customer_id, item_id
) x
WHERE rnk = 2;

Step by Step

1. Grouping by customer_id, item_id gives how many times each customer ordered each specific item.
2. DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY COUNT(*) DESC) ranks each customer's items by order frequency, restarting the ranking per customer.
3. Filtering to rnk = 2 keeps each customer's second most frequently ordered item.

Why DENSE_RANK() Rather Than ROW_NUMBER() Here

If a customer ordered two different items exactly the same number of times for their top frequency, ROW_NUMBER() would arbitrarily assign one of them rank 1 and the other rank 2 - incorrectly treating a tie for 1st place as if one item were genuinely "second most frequent." DENSE_RANK() correctly gives both tied items rank 1, and the next distinct frequency becomes rank 2 - the more semantically correct choice for "second most frequently ordered."

Key Exam Traps

• Choosing the right ranking function is the entire point of this question - a candidate reaching for ROW_NUMBER() without considering ties reveals a gap in understanding, even if the query runs without error.
• Customers with only one distinct item ordered have no row at rnk = 2 at all - the query correctly returns nothing for them rather than erroring, which is expected behavior worth stating explicitly.
• "Second most frequent" vs "second most recent" are easy to conflate - this question is explicitly about order count, not order date, and the ORDER BY COUNT(*) DESC inside the window function is what makes that distinction concrete.