SQL Exercise: Find largest number of orders booked by the customer
Customer Unique Orders and Maximum Purchase
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
ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
Sample Solution:
-- This query selects specific columns ('customer_id', the count of distinct 'ord_no', and the maximum 'purch_amt') from the 'orders' table.
-- It groups the result set by 'customer_id', counts the distinct 'ord_no' for each group, and finds the maximum 'purch_amt'.
-- Finally, it orders the result set by the second column (count of distinct 'ord_no') in descending order.
SELECT customer_id, COUNT(DISTINCT ord_no), MAX(purch_amt)
-- Specifies the table from which to retrieve the data (in this case, 'orders').
FROM orders
-- Groups the result set by 'customer_id', counting the distinct 'ord_no' for each group.
GROUP BY customer_id
-- Orders the result set by the second column (count of distinct 'ord_no') in descending order.
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:
For more Practice: Solve these Related Problems:
- Write a SQL query to group orders by customer_id, count distinct ord_no, and determine the maximum purch_amt, then sort by the order count in descending order.
- Write a SQL query to select customer_id, the count of unique orders, and the maximum purchase amount, filtering out customers with only one order.
- Write a SQL query to retrieve customer_id, count of distinct orders, and maximum purch_amt, sorting by the order count descending then by customer_id ascending.
- Write a SQL query to list each customer’s unique order count and highest purchase amount, ordering the results by the order count in descending order and excluding those with a maximum purchase below a specified threshold.
- Write a SQL query to display customer_id, number of unique orders, and highest purch_amt, sorting by the order count in descending order and including only customers with more than two orders.
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.