Write a query to count how many orders each customer placed and rank customers by order count within their city.

Answer

Count orders per customer, then rank customers by that count within their own city using RANK() or DENSE_RANK().

Solution

SELECT customer_id, city, order_count,
       RANK() OVER (PARTITION BY city ORDER BY order_count DESC) AS rank_in_city
FROM (
  SELECT c.customer_id, c.city, COUNT(o.order_id) AS order_count
  FROM Customers c
  LEFT JOIN Orders o ON o.customer_id = c.customer_id
  GROUP BY c.customer_id, c.city
) x;

Step by Step

1. The inner query joins Customers to Orders and counts orders per customer, grouped by customer and their city.
2. A LEFT JOIN is used specifically so customers with zero orders still appear, with COUNT(o.order_id) correctly returning 0 for them (counting a joined column skips NULLs automatically).
3. The outer query applies RANK() OVER (PARTITION BY city ORDER BY order_count DESC), restarting the ranking separately within each city.

Choosing RANK() vs DENSE_RANK()

Which one is "correct" depends entirely on what the business means by "rank" when customers tie - this is a judgment call to raise explicitly, not silently assume.

Key Exam Traps

• INNER JOIN vs LEFT JOIN: Using an inner join here would silently drop customers who have never placed an order, which usually isn't the intended behavior for "rank customers by order count."
• COUNT(*) vs COUNT(o.order_id): After a LEFT JOIN, COUNT(*) would incorrectly count 1 for a customer with zero orders (counting the single joined NULL row), while COUNT(o.order_id) correctly returns 0, since it skips NULL values.
• Partitioning granularity: Forgetting PARTITION BY city and just using a single global ORDER BY would rank customers across the entire dataset rather than within their own city - a very different result from what's asked.