Window Functions for Top Categories | Dataford Interview Questions

Your question is Window Functions for Top Categories. 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

QuickBooks Online analytics needs a regional view of completed product revenue. Write a PostgreSQL query that identifies the top three product categories by revenue within each sales region.

Requirements

Join orders to products and regions.
Include only completed orders placed during January 2025, using revenue defined as quantity * unit_price.
Aggregate revenue by region and product category, then rank categories within each region using a window function.
Return categories ranked 1 through 3, including ties, ordered by region and rank.

Schema

sales_orders

Column	Type	Description
order_idPK	INT	Unique order identifier
region_id	INT	Reference to the sales region
product_id	INT	Reference to the purchased product
quantity	INT	Number of units ordered
unit_price	DECIMAL(10,2)	Price per unit
order_status	VARCHAR(20)	Current order status
order_date	DATE	Date the order was placed

products

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

regions

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