SQL Exercises: Higher purchase amount by the customer within a list
SQL Aggregate Functions: Exercise-14 with Solution
From the following table, write a SQL query to find the maximum order (purchase) amount based on the combination of each customer and order date. Filter the rows for maximum order (purchase) amount is either 2000, 3000, 5760, 6000. Return customer id, order date and maximum purchase amount.
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 retrieves the maximum 'purch_amt' value for each combination of 'customer_id' and 'ord_date' from the 'orders' table.
-- It only includes rows where the maximum 'purch_amt' is one of the specified values (2000, 3000, 5760, 6000).
SELECT customer_id, ord_date, MAX(purch_amt)
-- Specifies the table from which to retrieve the data (in this case, 'orders').
FROM orders
-- Groups the result set by the 'customer_id' and 'ord_date' columns.
GROUP BY customer_id, ord_date
-- Filters the grouped results to only include those where the maximum 'purch_amt' is in the specified values (2000, 3000, 5760, 6000).
HAVING MAX(purch_amt) IN (2000, 3000, 5760, 6000);
Output of the Query:
customer_id ord_date max 3002 2012-09-10 5760.00
Code Explanation:
The said SQL query retrieves the customer id, order date, and the maximum purchase amount for each combination of customer id and order date in the 'orders' table.
The result will be grouped by customer id and order date, and only those groups with a maximum purchase amount equal to 2000, 3000, 5760, or 6000 will be returned.
The "HAVING" clause is used to specify the condition for the groups to be returned and the "IN" operator is used to define the set of values that the maximum purchase amount can be.
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: Higher purchase amount of customers within a range.
Next SQL Exercise: Highest purchase amount from customers.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sql-exercises/sql-aggregate-function-exercise-14.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics