How to find the product with the maximum discount using SQL on HackerRank

Question

I'm having trouble with an SQL problem on HackerRank (so no rank or window functions):

     Product_Id  Product_Name   Category  Price   Discount  Available
     1           P-1            C-5        720     10       1
     2           P-2            C-1        935     17       1
     3           P-3            C-2        588     19       1
     4           P-4            C-4        619     5        0
     5           P-5            C-1        803     16       1

I want to know which product is on the maximum discount for each category. In the case of multiple products having the same maximum discount within a category, print the product with the minimum product_id.

Sample Output

In this SQL Fiddle, I've put here what I've tried so far. I'm not sure how to get the product for the maximum discount. Also, how can I handle if there are multiple products with the same maximum discount?

score 4 · Answer 1 · edited Feb 03 '21 at 10:04

If you can’t use a window function, you can try using a subquery.

This example is for T-SQL:

drop table if exists #Product
go
CREATE TABLE #Product
    ([Product_Id] int, [Product_Name] varchar(50), [Category] varchar(50), [Price] int, [Discount] int, [Available] int)
;

INSERT INTO #Product
    ([Product_Id], [Product_Name], [Category], [Price], [Discount], [Available])
VALUES
    (1, 'P-1', 'C-5',720, 10, 1),
    (2, 'P-2', 'C-1',935, 17, 1),
    (3, 'P-3', 'C-2',588, 19, 1),
    (4, 'P-4', 'C-4',619, 5, 0),
    (5, 'P-5', 'C-1',803, 16, 1)

    select t2.[Category]
    ,(select min(t.Product_Id) from #Product t where  t.Category=t2.Category and t.Discount=max(t2.Discount))
    ,max(t2.Discount) from #Product t2
    group by [Category]

Perhaps [elaborate](https://stackoverflow.com/posts/66022004/edit) on "using a subquery"? E.g., *why* does it work and *how* does the solution here work (what is the idea/gist of it)? (***Without*** "Edit:", "Update:", or similar - the answer should appear as if it was written right now.) — Peter Mortensen, Feb 03 '21 at 10:07

Meysam Asadi · Answer 2 · 2021-02-03T10:36:38.777

This code returns the most discounted product in each group . This code links the product table to the dynamic table that returns the groups and the highest discount price. It then returns the products that have this group and the discount along with the queuing and at the end of the first row returns each group. Finally you can sort the rows by whatever you want.

WITH newTable AS (
SELECT 
t.Category,
t.Product_Id,
b.Discount as maxDiscount,   
ROW_NUMBER() OVER (
                         PARTITION BY t.Category 
                         ORDER BY Product_Id DESC
                   ) AS [ROW NUMBER]
FROM Products t
INNER JOIN 
(
  SELECT MAX(Discount) as maxDiscount, Category
  FROM Products
 GROUP BY Category
) b ON t.Discount = b.Discount AND t.Category = b.Category)
select Category,Product_Id,maxDiscount from newTable
WHERE newTable.[ROW NUMBER] = 1

I modified the post and entered an explanation of how to execute. — Meysam Asadi, Feb 03 '21 at 10:38

score 0 · Answer 3 · answered May 30 '22 at 15:57

I used the rank() window_function to get the order of products based on discount and product_id within every category. then put it within subquery because I can't run where clause on the window_function column.

select * from (
    select Category,Product_Id,
    rank() over (partition by category order by discount desc, product_id asc) as product_rank
    from Product) x
where x.product_rank=1
order by category;

Sanjay · Answer 4 · 2022-10-06T19:02:56.583

0

Selecting only the necessary columns:

select Category,Product_Id,discount from (
        select Category,Product_Id,discount,
        rank() over (partition by category order by discount desc, product_id asc) as product_rank
        from Product) x
    where x.product_rank=1
    order by category;

edited Oct 06 '22 at 19:02

answered Oct 06 '22 at 18:29

Sanjay

1
1

Shashank Joshi · Answer 5 · 2022-11-02T12:41:23.640

0

WITH product_rank_table AS
(
SELECT Category,Product_Id,discount, RANK() OVER(PARTITION BY category ORDER BY discount DESC, product_id ASC) AS product_rank FROM Product
)
SELECT Category,Product_Id,discount FROM product_rank_table AS x WHERE x.product_rank=1 ORDER BY category;

edited Nov 02 '22 at 12:41

answered Nov 02 '22 at 12:33

Shashank Joshi

1
1

Hey there! Welcome to stackoverflow! thank you so much for responding and answering to questions! please add some explanations to your answer to be more helpful. Thank you very much. – El.Hum Nov 05 '22 at 05:37

score 0 · Answer 6 · answered Aug 01 '23 at 11:45

It looks like it can be solved with:

SELECT p1.CATEGORY, p1.PRODUCT_ID, p1.DISCOUNT
FROM PRODUCT p1, (SELECT CATEGORY, max(DISCOUNT) as max_discount
                  FROM PRODUCT
                  GROUP BY CATEGORY) as p2
WHERE p1.CATEGORY = p2.CATEGORY
AND p1.DISCOUNT >= p2.max_discount
AND p1.PRODUCT_ID <= (SELECT min(PRODUCT_ID)
                      FROM PRODUCT
                      WHERE p1.CATEGORY = CATEGORY AND p1.DISCOUNT = DISCOUNT)
ORDER BY p1.CATEGORY asc;

You join the Product table with p2 - which is an aggregate table with columns (CATEGORY, max_discount). So, you know the max_discount in each category.

For Each row in p1, you match it with the category row in p2. and validate that p1 row is with the max_discount for that category. Than you build a secondary table/view, that will contain the minimal PROD_ID for p1.CATEGORY and p1.DISCOUNT. And check that the p1 entry/row will contain the minimal PROD_ID.

And Now, you order the results. :-)

How to find the product with the maximum discount using SQL on HackerRank

6 Answers6