Top Restaurants by Order Volume | Dataford Interview Questions

Swiggy's Business Analytics team wants to identify the restaurants with the highest completed order volume. Assume the report date is 2025-02-15, so the previous calendar month is January 2025.

Write a PostgreSQL query to return the top three restaurants by completed order volume during that month.

Requirements

Join restaurants to orders using restaurant_id.
Include only orders with status COMPLETED placed from 2025-01-01 through, but not including, 2025-02-01.
Return each restaurant's name and completed order count, ordered from highest to lowest. Break ties using ascending restaurant_id and return only three rows.

Schema

restaurants

Column	Type	Description
restaurant_idPK	INTEGER	Unique restaurant identifier
restaurant_name	VARCHAR(120)	Restaurant display name
city	VARCHAR(80)	Swiggy service city
is_active	BOOLEAN	Whether the restaurant is currently active

orders

Column	Type	Description
order_idPK	BIGINT	Unique order identifier
restaurant_id	INTEGER	Restaurant associated with the order
order_placed_at	TIMESTAMP	Timestamp when the order was placed
order_status	VARCHAR(20)	Order lifecycle status
order_value	NUMERIC(10,2)	Gross order value in INR

Your question is Top Restaurants by Order 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