w3resource

SQL Exercises: Find the 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

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

Pictorial 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: From the following table, write a SQL query to find all the customers. Sort the result-set in descending order on 3rd field. Return customer name, city and grade.
Next: From the following table, write a SQL query to calculate summation of purchase amount, total commission (15% for all salesmen) by each order date. Sort the result-set on order date. Return order date, summation of purchase amount and commission.

What is the difficulty level of this exercise?



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

How to restore a dump file from mysqldump?

It should be as simple as running this:

mysql -u <user> -p < db_backup.dump

If the dump is of a single database you may have to add a line at the top of the file:

USE <database-name-here>;

Ref: https://bit.ly/3xKmyw2