Top Customers by Purchase Volume | Dataford Interview Questions

Amazon DSP needs a customer-level view of purchase activity to prioritize audience analysis and optimization. Write a PostgreSQL query to find the 10 customers with the highest completed purchase volume.

Requirements

Count purchase volume as the sum of quantity from items belonging to orders with status = 'completed'.
Include customers with no completed purchases, treating their volume as zero.
Return the top 10 customers, ordered by purchase volume descending and customer_id ascending to break ties.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer display name

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order; NULL for unattributed orders
status	VARCHAR(20)	Order lifecycle status

order_items

Column	Type	Description
order_idPK	INT	Order associated with the item record
quantity	INT	Number of units in the order item

Your question is Top Customers by Purchase Volume. 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