Join and Rank Top Products | Dataford Interview Questions

Sigmoid Analytics wants to compare product performance across sales regions. Write a PostgreSQL query that joins product metadata with sales transactions and returns the highest-revenue products in each region.

Requirements

Include only completed sales from calendar year 2025 with positive quantities and non-null unit prices.
Calculate total revenue and units sold for each product and region.
Rank products within each region by total revenue and return every product with a rank of 2 or better, preserving ties.
Sort the final output by region, revenue rank, and product ID.

Schema

products

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(100)	Product name
category	VARCHAR(50)	Product category

sales

Column	Type	Description
sale_idPK	INT	Unique sale identifier
product_id	INT	Product associated with the sale
region	VARCHAR(50)	Sales region
quantity	INT	Number of units sold
unit_price	NUMERIC(10,2)	Price per unit
sale_date	DATE	Date of the sale
status	VARCHAR(20)	Sale processing status

Your question is Join and Rank Top Products. Start with the requirements and the two tables in the Question tab.

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

Problem