𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿:
You have 2 minutes to solve this SQL query.

Find the customer(s) who placed the highest number of orders.

Tables:
customers(customer_id, customer_name)
orders(order_id, customer_id, order_date)

𝗠𝗲: Challenge accepted! 💪

SELECT
    customer_id,
    customer_name,
    total_orders
FROM (
    SELECT
        c.customer_id,
        c.customer_name,
        COUNT(o.order_id) AS total_orders,
        DENSE_RANK() OVER (
            ORDER BY COUNT(o.order_id) DESC
        ) AS rnk
    FROM customers c
    JOIN orders o
        ON c.customer_id = o.customer_id
    GROUP BY
        c.customer_id,
        c.customer_name
) ranked
WHERE rnk = 1;

💡 Explanation:
The query first counts the total number of orders placed by each customer and then ranks them based on the order count.

✅ COUNT(o.order_id) calculates the number of orders per customer.

✅ GROUP BY ensures one row per customer.

✅ DENSE_RANK() ranks customers from highest to lowest order count.

✅ The outer query returns all customers with rnk = 1, including ties.

This question tests your understanding of:

✅ JOIN

✅ GROUP BY

✅ Aggregate Functions COUNT

✅ Window Functions DENSE_RANK

🎯 Expected Output Example

+----------+--------------+
| Customer | Total Orders |
+----------+--------------+
| John     | 25           |
| Sarah    | 25           |
+----------+--------------+

Both customers are returned because they are tied for the highest number of orders.

🚀 Tip for SQL Job Seekers:
Whenever an interview asks for the highest, lowest, most, or least, think about whether multiple records could tie for first place. Using DENSE_RANK() instead of LIMIT 1 makes your solution more robust and interview-ready.

❤️ React with ❤️ for more SQL interview challenges!