w3resource

SQL Exercise: Find largest number of orders booked by the customer

SQL Formatting Output: Exercise-9 with Solution

From the following table, write a SQL query that counts the unique orders and the highest purchase amount for each customer. Sort the result-set in descending order on 2nd field. Return customer ID, number of distinct orders and highest purchase amount by each customer.

Sample table: orders


Sample Solution:

SELECT customer_id, COUNT(DISTINCT ord_no), 
MAX(purch_amt) 
FROM orders 
GROUP BY customer_id 
ORDER BY 2 DESC;

Output of the Query:

customer_id	count		max
3002		3		5760.00
3009		2		2480.40
3005		2		948.50
3004		1		1983.43
3001		1		270.65
3007		1		2400.60
3008		1		250.45
3003		1		75.29

Code Explanation:

The said query in SQL that retrieves the customer id, count of distinct order numbers, and maximum purchase amount from the 'orders' table.
It is ordered by the number of distinct order numbers in descending order and grouped by the "customer_id" column within the result set.
This query returns the customer with the highest number of unique orders first and shows their customer id, the count of their unique orders, and the maximum amount spent on a single order.

Relational Algebra Expression:

Relational Algebra Expression: Find largest number of orders booked by the customer.

Relational Algebra Tree:

Relational Algebra Tree: Find largest number of orders booked by the customer.

Explanation:

make a report with customer ID in such a manner that, the largest number of orders booked by the customer will comes first along with their highest purchase amount

Visual presentation:

make a report with customer ID in such a manner that, the largest number of orders booked by the customer will comes first along with their highest purchase amount

Practice Online


Query Visualization:

Duration:

Query visualization of Find largest number of orders booked by the customer - Duration

Rows:

Query visualization of Find largest number of orders booked by the customer - Rows

Cost:

Query visualization of Find largest number of orders booked by the customer - Cost

 

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Display customer name, city and grade by highest grade.
Next SQL Exercise: Purchase amount and commission for the last order.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.