Language: SQL
sql
SELECT customer_id, COUNT(*) as order_count
FROM orders
WHERE order_date > '2024-01-01'
GROUP BY customer_id
ORDER BY order_count DESC
LIMIT 1;
Goal: find the customer with the most orders after Jan 1st, 2024.
Looks right at first glance - what's the subtle issue? 👇
.
.
.
The bug:
LIMIT 1 silently drops any ties. If TWO customers are tied for the most orders, this query arbitrarily returns just one of them (and which one is returned isn't guaranteed to be consistent across database engines or even across runs).If the actual requirement is "find ALL customers tied for the most orders," this query quietly gives a wrong (incomplete) answer that LOOKS correct.
Fixed version (handles ties):
sql
WITH ranked AS (
SELECT customer_id, COUNT(*) as order_count,
RANK() OVER (ORDER BY COUNT(*) DESC) as rnk
FROM orders
WHERE order_date > '2024-01-01'
GROUP BY customer_id
)
SELECT customer_id, order_count
FROM ranked
WHERE rnk = 1;
💡 This is a great example of why clarifying requirements matters even in SQL questions - "top 1" and "all customers tied for the top spot" are genuinely different problems, and a query that's correct for one is silently wrong for the other.
Have you ever shipped a query that "worked" but quietly handled ties incorrectly? 👇