SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE stores
    (StoreID int, Location varchar(13), StoreName varchar(8))
;
    
INSERT INTO stores
    (StoreID, Location, StoreName)
VALUES
    (1, 'San Diego', 'Ralph''s'),
    (2, 'San Francisco', 'Ralph''s'),
    (3, 'Sacramento', 'Ralph''s')
;




CREATE TABLE products
    (ProductID int, Name varchar(10), Category varchar(5))
;
    
INSERT INTO products
    (ProductID, Name, Category)
VALUES
    (1, 'Milk', 'Food'),
    (2, 'Eggs', 'Food'),
    (3, 'Reese''s', 'Candy'),
    (4, 'Hershey''s', 'Candy')
;


CREATE TABLE sales
    (SaleID int, ProductID int, StoreID int, Quantity int)
;
    
INSERT INTO sales
    (SaleID, ProductID, StoreID, Quantity)
VALUES
    (1, 1, 1, 4),
    (2, 1, 1, 2),
    (3, 2, 2, 2),
    (4, 3, 3, 3)
;
11 rows affected

select *
from   sales s join products p on p.ProductID = s.ProductID join stores st on st.StoreID = s.StoreID


SaleID
ProductID
StoreID
Quantity
ProductID
Name
Category
StoreID
Location
StoreName


1
1
1
4
1
Milk
Food
1
San Diego
Ralph's

2
1
1
2
1
Milk
Food
1
San Diego
Ralph's

3
2
2
2
2
Eggs
Food
2
San Francisco
Ralph's

4
3
3
3
3
Reese's
Candy
3
Sacramento
Ralph's


select  StoreID 
       ,Location	
       ,ProductID	
       ,Name	
       ,Quantity 
from    (
        select  *
                ,rank() over(partition by StoreID order by quantity desc) as rnk
        from    (
                select   s.StoreID
                        ,st.Location
                        ,s.ProductID
                        ,p.Name
                        ,sum(Quantity) as Quantity
                from     sales s join products p on p.ProductID = s.ProductID join stores st on st.StoreID = s.StoreID
                group by s.StoreID, st.Location, s.ProductID, p.Name
               ) t
      ) t
where rnk = 1


StoreID
Location
ProductID
Name
Quantity


1
San Diego
1
Milk
6

2
San Francisco
2
Eggs
2

3
Sacramento
3
Reese's
3

SaleID	ProductID	StoreID	Quantity	ProductID	Name	Category	StoreID	Location	StoreName
1	1	1	4	1	Milk	Food	1	San Diego	Ralph's
2	1	1	2	1	Milk	Food	1	San Diego	Ralph's
3	2	2	2	2	Eggs	Food	2	San Francisco	Ralph's
4	3	3	3	3	Reese's	Candy	3	Sacramento	Ralph's