Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dim_product (
    product_id int4 null primary key,
    product_name varchar(100) NULL
);
CREATE TABLE dim_territory (
    territory_id int4 null primary key,
    region varchar(30) NULL
);    
CREATE TABLE fact_sales (
    order_detail_id varchar(30) null primary key,
    product_id int4 NULL,
    territory_id int4 NULL,
    quantity int4 NULL,
    foreign key(product_id) references dim_product(product_id),
    foreign key(territory_id) references dim_territory(territory_id)
);

insert into dim_product 
(product_id, product_name)
values
(1, 'patch kit'), 
(2, 'mountain bike'), 
(3, 'logo');        
insert into dim_territory 
(territory_id, region)
values
(1, 'AUS'),
(2, 'FRN'),
(3, 'GRMN');    
insert into fact_sales
(order_detail_id, product_id, territory_id, quantity)
values
('z1', 1, 1, 42),
('z2', 1, 1, 39),
('z3', 2, 1, 4),
('z4', 3, 2, 45),
('z5', 3, 2, 2),
('y1', 2, 1, 1),
('y2', 1, 3, 83),
('y3', 2, 3, 5),
('x4', 3, 3, 1),
('x5', 3, 2, 4);
3 rows affected

3 rows affected

10 rows affected

SELECT *, RANK() OVER w AS rank
FROM (
  SELECT DT.region, DP.product_name, sum(FS.quantity) AS quantity
  FROM fact_sales FS 
  JOIN dim_territory DT ON FS.territory_id=DT.territory_id
  JOIN dim_product DP ON FS.product_id=DP.product_id
  GROUP BY product_name, region
) j 
WINDOW w AS (PARTITION BY region ORDER BY quantity DESC
             RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)


region
product_name
quantity
rank


AUS
patch kit
81
1

AUS
mountain bike
5
2

FRN
logo
51
1

GRMN
patch kit
83
1

GRMN
mountain bike
5
2

GRMN
logo
1
3


WITH j AS (
  SELECT *, RANK() OVER w AS rank
  FROM (
    SELECT DT.region, DP.product_name, sum(FS.quantity) AS quantity
    FROM fact_sales FS 
    JOIN dim_territory DT ON FS.territory_id=DT.territory_id
    JOIN dim_product DP ON FS.product_id=DP.product_id
    GROUP BY product_name, region
  ) i 
  WINDOW w AS (PARTITION BY region ORDER BY quantity DESC
               RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
)
SELECT * FROM j WHERE rank <= 2


region
product_name
quantity
rank


AUS
patch kit
81
1

AUS
mountain bike
5
2

FRN
logo
51
1

GRMN
patch kit
83
1

GRMN
mountain bike
5
2
region	product_name	quantity	rank
AUS	patch kit	81	1
AUS	mountain bike	5	2
FRN	logo	51	1
GRMN	patch kit	83	1
GRMN	mountain bike	5	2
GRMN	logo	1	3