Top Merchants Per City SQL | Dataford Interview Questions

Your question is Top Merchants Per City SQL. Start with the requirements and the two 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

DoorDash wants to identify the highest-volume merchants in each city using completed orders. Write a PostgreSQL query that returns the top three merchants by total sales amount within every city.

Requirements

Join orders to merchants to display merchant names.
Include only orders with order_status = 'completed' and a non-null city.
Aggregate sales by city and merchant, then rank merchants independently within each city.
Return at most three merchants per city, ordered by city, total sales descending, and merchant name as a deterministic tie-breaker.

Schema

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
merchant_id	INT	Merchant associated with the order
city	VARCHAR(100)	Delivery city
order_status	VARCHAR(20)	Order state, such as completed or cancelled
order_total	DECIMAL(10,2)	Customer order amount

merchants

Column	Type	Description
merchant_idPK	INT	Unique merchant identifier
merchant_name	VARCHAR(150)	Merchant display name