Window Functions for Top Records | Dataford Interview Questions

Virtusa's delivery portfolio team needs to identify the highest-performing services within selected categories. Write a PostgreSQL query that aggregates completed sales and ranks services independently within the Cloud and Data & AI categories.

Requirements

Include every service in the selected categories, including services with no completed sales.
Calculate total completed sales revenue per service, treating missing revenue as zero and excluding cancelled sales.
Use a window function to rank services within each category and return services with rank 1 or 2. Preserve ties at the rank boundary.
Sort the output by category, rank, total revenue descending, and service name.

Schema

products

Column	Type	Description
product_idPK	INT	Unique service identifier
product_name	VARCHAR(120)	Virtusa delivery service name
category	VARCHAR(80)	Service category
active	BOOLEAN	Whether the service is currently offered

sales_records

Column	Type	Description
sale_idPK	INT	Unique sales record identifier
product_id	INT	Referenced service, nullable for unassigned sales
amount	DECIMAL(12,2)	Sale value
status	VARCHAR(20)	Sale processing status

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