Top Products Per Region Query

MediumSQL · PostgreSQL

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