Top 5 Products SQL Query | Dataford Interview Questions

Jerry wants to identify the products generating the most sales on its customer and policy surfaces. Write a PostgreSQL query that returns the top five products by sales during the rolling 12-month period ending on August 29, 2026.

Requirements

Include only orders with status = 'completed'.
Include orders from DATE '2025-08-29' through DATE '2026-08-29'.
Calculate sales as quantity * unit_price, treating a missing unit price as zero.
Return product name and total sales, sorted from highest to lowest. Break ties by product_id ascending.

Output

One row per product, with columns product_name and total_sales.
Include only completed orders within the specified one-year period.
Treat a NULL unit price as zero.
Sort by total sales descending, break ties by product ID ascending, and return at most five rows.

Schema

products

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(100)	Jerry product name

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order lifecycle status

order_items

Column	Type	Description
order_item_idPK	INT	Unique order-line identifier
order_id	INT	References orders.order_id
product_id	INT	References products.product_id
quantity	INT	Number of units purchased
unit_price	NUMERIC(10,2)	Price per unit at checkout

Your question is Top 5 Products 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