Top 3 Revenue by Region | Dataford Interview Questions

Newton School of Technology wants a regional revenue summary for its course enrollments. Write a PostgreSQL query that calculates the sum of the three highest valid order revenues within each region.

Requirements

Join orders with regions using region_id.
Rank orders independently within each region by revenue descending. Break revenue ties with the smaller order_id.
Exclude orders with NULL revenue, keep only the top three orders per region, and return the regional sum.
Sort the final output by summed revenue descending, then region name ascending.

Schema

regions

Column	Type	Description
region_idPK	INT	Primary key for a Newton School operating region
region_name	VARCHAR(50)	Name of the operating region

orders

Column	Type	Description
order_idPK	INT	Primary key for an enrollment order
region_id	INT	Region associated with the order
revenue	DECIMAL(12,2)	Revenue generated by the order

Your question is Top 3 Revenue by Region. 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