Window Functions SQL Query | Dataford Interview Questions

Eli Lilly’s commercial analytics team wants to compare completed prescription revenue for Lilly products across U.S. regions. Write a PostgreSQL query that ranks products within each region for the first quarter of 2025.

Requirements

Include only completed prescription records dated from 2025-01-01 through 2025-03-31.
Calculate total net revenue for each region and product.
Use a window function to rank products within each region by total revenue descending, returning the top two ranked products per region. Include ties when applicable.
Return regions with qualifying prescription activity, ordered by region and rank.

Schema

regions

Column	Type	Description
region_idPK	INT	Unique sales region identifier
region_name	VARCHAR(50)	U.S. sales region name

products

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

prescription_sales

Column	Type	Description
sale_idPK	INT	Unique prescription sales record identifier
region_id	INT	References regions.region_id
product_id	INT	References products.product_id
sale_date	DATE	Prescription record date
net_revenue	DECIMAL(12,2)	Net revenue in U.S. dollars
status	VARCHAR(20)	Processing status of the record

Your question is Window Functions SQL Query. Start with the requirements and the three 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