SQL Query for Top Products | Dataford Interview Questions

Business context

Natural Intelligence uses product-level revenue reporting to evaluate performance across its comparison and betting surfaces. Assume the report is run on 2025-01-15, so the last completed quarter is Q4 2024.

Task

Write a PostgreSQL query that returns the top five products by completed revenue during the last completed calendar quarter.

Requirements

Join products to their categories and transactions.
Include only transactions with status = 'completed' within the quarter ending before the reporting date.
Aggregate revenue by product, order from highest to lowest revenue, and use product_id as a deterministic tie-breaker.

Schema

ni_products

Column	Type	Description
product_idPK	INT	Unique Natural Intelligence product identifier
product_name	VARCHAR(100)	Product or partner brand name
category_id	INT	Identifier of the product category

ni_product_categories

Column	Type	Description
category_idPK	INT	Unique category identifier
category_name	VARCHAR(100)	Product category label

ni_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
product_id	INT	Product associated with the transaction
transaction_date	DATE	Date on which the transaction occurred
revenue	NUMERIC(12,2)	Revenue attributed to the transaction
status	VARCHAR(20)	Transaction processing status

Your question is SQL Query for Top Products. 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