Top Products Per Region Query

MediumSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is Top Products Per Region Query. Start with the requirements and the four 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.

You need to log in / sign up to run or submit.

Problem

Apex Systems wants a quarterly view of product performance in its sales analytics reporting. Write a PostgreSQL query that identifies the highest-grossing products in each sales region for the last completed quarter, defined here as April 1, 2025 through June 30, 2025.

Requirements

Join regions, orders, order_items, and products to calculate product revenue by region.
Include only orders whose order_date falls within the specified quarter.
Calculate gross revenue as quantity * unit_price and aggregate it by region and product.
Use a window function to return all products whose rank is within the top three positions per region. Products tied at a qualifying rank must all be included.
Return region_name, product_name, gross_revenue, and the ranking value, ordered by region and revenue descending.

Schema

regions

Column	Type	Description
region_idPK	INT	Unique sales region identifier
region_name	VARCHAR(50)	Sales region name
region_manager	VARCHAR(100)	Assigned region manager

products

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

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
region_id	INT	Region associated with the order
order_date	DATE	Order placement date

order_items

Column	Type	Description
order_item_idPK	INT	Unique order line identifier
order_id	INT	Parent order identifier
product_id	INT	Sold product identifier
quantity	INT	Units sold
unit_price	NUMERIC(10,2)	Selling price per unit

Tablesregionsproductsordersorder_items

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results

Top Products Per Region Query

MediumSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is Top Products Per Region Query. Start with the requirements and the four 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.

You need to log in / sign up to run or submit.

Problem

Requirements

Join regions, orders, order_items, and products to calculate product revenue by region.
Include only orders whose order_date falls within the specified quarter.
Calculate gross revenue as quantity * unit_price and aggregate it by region and product.
Use a window function to return all products whose rank is within the top three positions per region. Products tied at a qualifying rank must all be included.
Return region_name, product_name, gross_revenue, and the ranking value, ordered by region and revenue descending.

Schema

regions

Column	Type	Description
region_idPK	INT	Unique sales region identifier
region_name	VARCHAR(50)	Sales region name
region_manager	VARCHAR(100)	Assigned region manager

products

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

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
region_id	INT	Region associated with the order
order_date	DATE	Order placement date

order_items

Column	Type	Description
order_item_idPK	INT	Unique order line identifier
order_id	INT	Parent order identifier
product_id	INT	Sold product identifier
quantity	INT	Units sold
unit_price	NUMERIC(10,2)	Selling price per unit

Tablesregionsproductsordersorder_items

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results