Customer Placing the Largest Number of Orders

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

table name: Orders

Question Explanation: 

• We have given a table named orders in which order_number is primary key. But customer_number can have repeated  values based on how many times customer has ordered something.. 
• Each time when a particular customer will order something a new record is going to be added with particular cutomer_number and unique order_number.
• Our task is to find a customer who has placed order maximum times.

Solution Explanation:

• Since, customer_number is repeated multiple times. We are going to apply group by clause on customer_number. It wil create different groups for each unique customer_number.

• Then we will count number of rows in each group.

select COUNT(order_number),customer_number from Orders
group by customer_number 

• Order by count(rows) descending.

select COUNT(order_number),customer_number from Orders
group by customer_number 
ORDER BY COUNT(order_number) desc

• Select top 1 customer_number.

Final Solution:

select top 1 customer_number from Orders
group by customer_number 
ORDER BY COUNT(order_number) desc

Output:

SQL Script:

CREATE TABLE [dbo].[Orders](
       [order_number]
[int] NULL,
       [customer_number]
[int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Orders] ([order_number], [customer_number]) VALUES (1, 1)
GO
INSERT [dbo].[Orders] ([order_number], [customer_number]) VALUES (2, 2)
GO
INSERT [dbo].[Orders] ([order_number], [customer_number]) VALUES (3, 3)
GO
INSERT [dbo].[Orders] ([order_number], [customer_number]) VALUES (4, 3)
GO