A department store maintains data on customers, products, and purchase records in three tables: customer, product, and purchase. the store manager wants to know which product is on maximum discount for each category. write a query to print the following fields for each category, ordered by category, ascending: category, product id and discount for the product that has the maximum discount in the category. in the case of multiple products having same maximum discount within a category, print the product with minimum product_id.

Question

contestada

Respuesta :

ACCESS MORE ACCESS MORE ACCESS MORE ACCESS MORE

jayilych4real jayilych4real · Answer 1 · 2022-07-21T19:40:52+02:00

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

C-1 2 17

C-2 3 19

C-4 4 5

C-5 1 10

The code that would print the fields in SQL for the maximum discount is:

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