Home > Net >  Finding the 3 top selling products in each category
Finding the 3 top selling products in each category

Time:10-12

I am trying to find the 3 top selling products in each category. But the SQL I tried does not yield the correct results. Here's the things I did:

Products Table:

CREATE TABLE [dbo].[Products](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](100) NOT NULL,
    [category] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)
id name category
1 prod1 cat1
2 prod2 cat1
3 prod3 cat1
4 prod4 cat1
5 prod5 cat1
6 prod6 cat2
7 prod7 cat2
8 prod8 cat2
10 prod10 cat2

OrderItems table:

CREATE TABLE [dbo].[OrderItems](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [prodID] [int] NOT NULL,
    [quantity] [int] NOT NULL,
 CONSTRAINT [PK_OrderItems] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)

ALTER TABLE [dbo].[OrderItems]  WITH CHECK ADD  CONSTRAINT [FK_OrderItems_Products] FOREIGN KEY([prodID])
REFERENCES [dbo].[Products] ([id])

ALTER TABLE [dbo].[OrderItems] CHECK CONSTRAINT [FK_OrderItems_Products]
id prodID quantity
1 1 10
2 2 6
3 2 6
4 4 3
5 3 2
6 6 10
7 7 1

SQL I tried:

select *
from (select p1.id, p1.category, (select sum(quantity) from OrderItems as oi where oi.prodID = p1.id) as sales
from Products as p1) as p2
where p2.sales >= (
    select distinct(sales)
    from (select p1.id, p1.category, (select sum(quantity) from OrderItems as oi where oi.prodID = p1.id) as sales
from Products as p1) as p3
    where p2.category = p3.category
    order by sales desc
    offset 2 rows fetch next 1 rows only)

Expected Result:

id category sales
1 cat1 10
2 cat1 12
4 cat1 3
6 cat2 10
7 cat2 1

Actual Result:

id category sales
1 cat1 10
2 cat1 12
4 cat1 3

What is the mistake in the query? And is there a better way to achieve the result?

CodePudding user response:

You can join the tables and aggregate to get the total sales for each product.
Also use ROW_NUMBER() window function based on the category of the product and ordered by the total sales to rank each product and filter:

SELECT id, category, sales
FROM (
  SELECT p.id, 
         MAX(category) category, 
         SUM(o.quantity) sales,
         ROW_NUMBER() OVER (PARTITION BY MAX(category) ORDER BY SUM(o.quantity) DESC) rn
  FROM Products p INNER JOIN OrderItems o
  ON o.prodID = p.id
  GROUP BY p.id
) t
WHERE rn <= 3
ORDER BY id;

Or, aggregate first in OrderItems and then join:

SELECT id, category, sales
FROM (
  SELECT p.id, 
         p.category, 
         o.sales,
         ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY o.sales DESC) rn
  FROM Products p 
  INNER JOIN (
    SELECT prodID, SUM(quantity) sales
    FROM OrderItems
    GROUP BY prodID
  ) o ON o.prodID = p.id
) t
WHERE rn <= 3
ORDER BY id;

See the demo.

  • Related