w3resource

SQL Challenges-1: Highest purchase for each customer

SQL Challenges-1: Exercise-38 with Solution

From the following table write a SQL query to find the highest purchase with its corresponding item for each customer. In case of a same quantity purchase find the item code which is smallest.
The output must be sorted by increasing of customer_id. Return customer ID,lowest item code and purchase quantity.

Input:

Table: purchase

Structure:

FieldTypeNullKeyDefaultExtra
customer_idint(11)NO
item_codeint(11)NO
purch_qtyint(11)NO

Data:

customer_iditem_codepurch_qty
10150425
10150350
10250240
10250325
10250145
10350530
10350325
10450540
10150225
10250440
10250550
10350225
10450440
10350135

Sample Solution:

SQL Code(MySQL):

CREATE TABLE purchase (customer_id int not null, item_code int not null, purch_qty int not null);
INSERT INTO purchase VALUES (101,504,25 );
INSERT INTO purchase VALUES (101,503,50 );
INSERT INTO purchase VALUES (102,502,40 );
INSERT INTO purchase VALUES (102,503,25 );
INSERT INTO purchase VALUES (102,501,45 );
INSERT INTO purchase VALUES (103,505,30 );
INSERT INTO purchase VALUES (103,503,25 );
INSERT INTO purchase VALUES (104,505,40 );
INSERT INTO purchase VALUES (101,502,25 );
INSERT INTO purchase VALUES (102,504,40 );
INSERT INTO purchase VALUES (102,505,50 );
INSERT INTO purchase VALUES (103,502,25 );
INSERT INTO purchase VALUES (104,504,40 );
INSERT INTO purchase VALUES (103,501,35 );

SELECT customer_id, min(item_code) as 'lowest item code', purch_qty
FROM purchase
WHERE (customer_id, purch_qty) IN (SELECT customer_id, max(purch_qty) 
FROM purchase
GROUP BY customer_id)
GROUP BY customer_id, purch_qty
ORDER BY customer_id;

Sample Output:

customer_id|lowest item code|purch_qty|
-----------|----------------|---------|
        101|             503|       50|
        102|             505|       50|
        103|             501|       35|
        104|             504|       40|

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Sale history of items for a particular period.
Next: Find all the writers who rated at least one of their own topic.



SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

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