Second-Highest Order Per Restaurant | Dataford Interview Questions

Write a complex SQL query involving multiple joins, subqueries, and window functions to find the second-highest order value per restaurant on Zomato.

Use delivered orders and calculate each order's value from its menu items and quantities. Return restaurants that have a second-highest distinct value, including all orders tied at that value.

Output

One row per qualifying order, with restaurant_id, restaurant_name, order_id, and order_value.
Sort by restaurant_id, then order_id ascending.

Schema

restaurants

Column	Type	Description
restaurant_idPK	INT	Unique restaurant identifier
restaurant_name	VARCHAR(100)	Restaurant display name
city	VARCHAR(80)	Restaurant city

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
restaurant_id	INT	Restaurant receiving the order
status	VARCHAR(20)	Order fulfillment status
ordered_at	TIMESTAMP	Order creation timestamp

order_items

Column	Type	Description
order_item_idPK	INT	Unique order-line identifier
order_id	INT	Related order
menu_item_id	INT	Ordered menu item
quantity	INT	Number of units ordered

menu_items

Column	Type	Description
menu_item_idPK	INT	Unique menu item identifier
restaurant_id	INT	Restaurant offering the item
item_name	VARCHAR(100)	Menu item name
price	NUMERIC(10,2)	Current item price

Your question is Second-Highest Order Per Restaurant. Start with the requirements and the four 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