Customer Placing the Largest Number of Orders is the question that is asked in interviews on Facebook, Google, Amazon, etc. 

This is a very interesting question and you should try this before checking the solution.


Let's start with the SQL Schema of the table named orders:


CREATE TABLE orders (order_number int, customer_number int)


INSERT INTO orders (order_number, customer_number) values ('1', '1')

INSERT INTO orders (order_number, customer_number) values ('2', '2')

INSERT INTO orders (order_number, customer_number) values ('3', '3')

INSERT INTO orders (order_number, customer_number) values ('4', '3')


The orders table will look like this:


order_number customer_number
1 1
2 2
3 3
4 3


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

The test cases are generated so that exactly one customer will have placed more orders than any other customer.

The result table will look like this:

 
customer_number
3


Now, let's start solving this by writing the algorithm.

First, we will get customer_number and count of their orders:

SELECT customer_number, count(*) FROM orders GROUP BY customer_number;


The result will look like this:

customer_number count(*)
1 1
2 1
3 2


You can see there is customer number 3 who has the maximum number of orders with 2. 
So, we will implement the LIMIT clause with ORDER BY DESC to get the correct output.

SELECT customer_number, count(*) FROM orders GROUP BY customer_number ORDER BY COUNT(*) DESC LIMIT 1;  

The result will look like this:


customer_number count(*)
3 2

But in the output, we only require the customer_number column. So, the query will look like,

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


This is a query that will produce the correct output. I hope you understood the algorithm and if you have any other way to solve this please let us know in the comments.

If you have any doubts or queries, let us know in the comments.