Top Products SQL by Category

MediumSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is Top Products SQL by Category. Start with the requirements and the three 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

HCLTech's retail analytics team needs a quarterly product leaderboard for executive reporting. Write a PostgreSQL query that evaluates product sales during the previous completed calendar quarter relative to the current date.

Calculate total units sold and total revenue for each product, associate every product with its category, and assign a position within that category. Revenue is calculated as quantity * unit_price for each order item.

Output

One row per qualifying product, with columns category_name, product_name, total_units_sold, total_revenue, and revenue_rank.
Include only products with sales in the previous completed calendar quarter.
Return no more than three products per category.
Sort by category name alphabetically, then leaderboard position ascending.
Within each category, order by total revenue descending, total units sold descending, and product name ascending to resolve ties.

Schema

Column	Type	Description
category_idPK	INT	Primary key for the product category
category_name	VARCHAR(100)	Display name of the category

Column	Type	Description
product_idPK	INT	Primary key for the product
category_id	INT	References categories.category_id
product_name	VARCHAR(150)	Display name of the product

Column	Type	Description
order_item_idPK	INT	Primary key for the order line
product_id	INT	References products.product_id
order_date	DATE	Date on which the item was sold
quantity	INT	Number of units sold
unit_price	NUMERIC(10,2)	Sale price per unit