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 Tree:

Explanation:

Visual presentation:

Practice Online

Query Visualization:

Duration:

Rows:

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.