SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [Products](
    [id] [int] NOT NULL,
    [name] [nvarchar](100) NOT NULL,
    [category] [nvarchar](100) NOT NULL,
    CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED ([id] ASC)
) 
CREATE TABLE [OrderItems](
    [id] [int] NOT NULL,
    [prodID] [int] NOT NULL,
    [quantity] [int] NOT NULL,
 CONSTRAINT [PK_OrderItems] PRIMARY KEY CLUSTERED ([id] ASC)
) 

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

ALTER TABLE [OrderItems] CHECK CONSTRAINT [FK_OrderItems_Products]
INSERT INTO Products([id], [name], [category]) VALUES
    (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');
9 rows affected

INSERT INTO OrderItems([id], [prodID], [quantity]) VALUES
    (1, 1, 10),
    (2, 2, 6),
    (3, 2, 6),
    (4, 4, 3),
    (5, 3, 2),
    (6, 6, 10),
    (7, 7, 1);
7 rows affected

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;


id
category
sales


1
cat1
10

2
cat1
12

4
cat1
3

6
cat2
10

7
cat2
1


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;


id
category
sales


1
cat1
10

2
cat1
12

4
cat1
3

6
cat2
10

7
cat2
1