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

user_13078629 0
Viewed 15183

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

    C-1 2 17
    C-2 3 19
    C-4 4 5
    C-5 1 10

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?

max sql
3 Answers

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]
user_147096070

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
user_150195360

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;
user_95866740
Related