SQL Top 5 Products by Region | Dataford Interview Questions

Your question is SQL Top 5 Products by Region. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Walgreens Boots Alliance wants a regional view of its highest-selling products. Using product, region, and sales data, write a PostgreSQL query to return the top five products by completed sales in each region.

Requirements

Join the sales, products, and regions tables using their key relationships.
Consider only sales with sale_status = 'Completed' and calculate total sales by region and product.
Rank products independently within each region using a window function, then return ranks 1 through 5.
Return the region, product, total sales, and rank, ordered by region and rank. Products with no completed sales should not appear.

Schema

regions

Column	Type	Description
region_idPK	INT	Unique regional identifier
region_name	VARCHAR(100)	Walgreens operating region name

products

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(150)	Product name
category	VARCHAR(80)	Product category

sales

Column	Type	Description
sale_idPK	INT	Unique sale identifier
region_id	INT	Region where the sale occurred
product_id	INT	Product sold, nullable for an unclassified sale
sale_amount	NUMERIC(12,2)	Revenue amount for the sale
sale_status	VARCHAR(30)	Transaction status