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
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
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:
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.
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join