Top Three Products by Region | Dataford Interview Questions

Your question is Top Three 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

Colgate-Palmolive wants a regional view of product performance for the most recently completed calendar quarter. Write a PostgreSQL query that identifies the top-selling products in each sales region.

Requirements

Filter sales to the most recently completed calendar quarter relative to CURRENT_DATE.
Join sales to regions and products to return readable region and product names.
Calculate total units sold for each region and product.
Rank products within each region by total units sold in descending order.
Return every product whose rank is three or better, including all products tied at the third rank. Display region_name, product_name, total_units_sold, and product_rank.
Order the final output by region, rank, and product name.

Schema

regions

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

products

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(150)	Colgate-Palmolive product name

sales

Column	Type	Description
sale_idPK	INT	Unique sales transaction identifier
region_id	INT	References regions.region_id
product_id	INT	References products.product_id
sale_date	DATE	Date on which the sale occurred
units_sold	INT	Number of units sold