clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591588 fiddles created (45742 in the last week).

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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)