SQL Exercises: Find largest number of orders booked by the customer
SQL Formatting Output : Exercise-9 with Solution
Write a SQL statement to make a report with customer ID in such a manner that, the largest number of orders booked by the customer will come first along with their highest purchase amount.
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
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Write a SQL statement to display customer name, city and grade in such a manner that, the customer holding highest grade will come first.
Next: Write a SQL statement make a report with order date in such a manner that, the latest order date will come last along with the total purchase amount and total commission (15% for all salesmen) for that date.
What is the difficulty level of this exercise?