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.

Rating: 1 out of 5.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s