Top Products by Monthly Volume | Dataford Interview Questions

DHL Supply Chain needs a monthly view of which products generate the highest shipment volume. Write a PostgreSQL query that ranks products within each calendar month using completed shipment quantities.

Requirements

Join shipment records to the product master and aggregate quantity by month and product.
Include only shipments with status = 'Completed'.
Return the top three products for each month, using ROW_NUMBER() and ordering ties alphabetically by product name.
Return the month, product name, total shipment volume, and rank, ordered by month and rank.

Schema

shipments

Column	Type	Description
shipment_idPK	INT	Unique shipment identifier
product_id	INT	Product associated with the shipment
shipment_date	DATE	Date on which the shipment was recorded
quantity	INT	Number of product units shipped
status	VARCHAR(20)	Shipment processing status

products

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(100)	Product name
product_category	VARCHAR(50)	Product classification

Your question is Top Products by Monthly Volume. 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