Amazon SQL interview question – Leetcode 586 – Customer Placing the Largest Number of Orders

Problem Description –

Write an SQL query to find the customer_number for the customer who has placed the largest number of orders.

It is guaranteed that exactly one customer will have placed more orders than any other customer.

The query result format is in the following example:

Difficulty Level – Easy

Solution –

SELECT
    customer_number
FROM Orders
GROUP BY 1
ORDER BY COUNT(*) DESC
LIMIT 1

Here, we are applying a little bit of shortcut method to get the result. Basically we grouping the data by customer number and counting how many orders that they have placed. But as the question doesn’t asks us to show the number of orders, we are not including the COUNT(*) as num_orders in the select clause and in the ORDER BY clause we are using the COUNT(*) DESC and Limiting the result to only one row as we want to find the customers with highest number of orders, we don’t want the second highest, 3rd highest and so on.