Reporting Query With Joins and Aggregates | Dataford Interview Questions

Your question is Reporting Query With Joins and Aggregates. 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.

Problem

Caterpillar's parts distribution team needs a quarterly report for Midwest dealers. Write a PostgreSQL query that summarizes completed parts orders from January 1 through March 31, 2025, while retaining Midwest dealers with no qualifying activity.

Requirements

Use LEFT JOIN to preserve dealers without completed orders or order items.
Include the number of completed orders, total units, total revenue, and average order value for each Midwest dealer.
Treat missing quantities, items, and revenue as zero, and sort by total revenue descending, then dealer name ascending.

Schema

dealers

Column	Type	Description
dealer_idPK	INT	Unique Caterpillar dealer identifier
dealer_name	VARCHAR(100)	Dealer business name
region	VARCHAR(50)	Dealer sales region

parts_orders

Column	Type	Description
order_idPK	INT	Unique parts order identifier
dealer_id	INT	Dealer associated with the order
order_date	DATE	Order placement date
status	VARCHAR(20)	Current order status

order_items

Column	Type	Description
item_idPK	INT	Unique order line identifier
order_id	INT	Related parts order
part_number	VARCHAR(30)	Caterpillar part number
quantity	INT	Number of units ordered
unit_price	NUMERIC(10,2)	Price per unit