Dataford
Popular roles
Software EngineerData AnalystData ScientistData EngineerBusiness AnalystAI EngineerMachine Learning EngineerProduct Manager
Browse
Browse All RolesEvery role hub, from analyst to MLBrowse All CompaniesCompany-specific interview loopsAll Interview GuidesThe full guide library
Top questions by role
Software EngineerData AnalystData ScientistData EngineerBusiness AnalystAI EngineerMachine Learning EngineerProduct Manager
Top questions by skill
SQLPythonStatisticsMachine LearningA/B TestingSystem DesignGenerative AIProduct SenseMetricsBehavioral
Browse all questions →Try a mock interview
ExperiencesCoaching
New
Practice
Mock InterviewsTimed interview simulations with feedbackSuccess PathYour 6-week structured planModulesCurated lessons by topicWebinarsTalks from ex-Big Tech data leadsPlaygroundA free-form scratch editor
Learn
BlogInterview strategy and career adviceTech Job Market ReportHiring trends across data and AI rolesFor UniversitiesDataford for career centersAbout DatafordWho we are and how we build
Pricing
Get started
Second-Highest Order Per Restaurant
00:00
5 left

Second-Highest Order Per Restaurant

HardSQL · PostgreSQL

Problem

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

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

Schema

restaurants
ColumnTypeDescription
restaurant_idPKINTUnique restaurant identifier
restaurant_nameVARCHAR(100)Restaurant display name
cityVARCHAR(80)Restaurant city
orders
ColumnTypeDescription
order_idPKINTUnique order identifier
restaurant_idINTRestaurant receiving the order
statusVARCHAR(20)Order fulfillment status
ordered_atTIMESTAMPOrder creation timestamp
order_items
ColumnTypeDescription
order_item_idPKINTUnique order-line identifier
order_idINTRelated order
menu_item_idINTOrdered menu item
quantityINTNumber of units ordered
menu_items
ColumnTypeDescription
menu_item_idPKINTUnique menu item identifier
restaurant_idINTRestaurant offering the item
item_nameVARCHAR(100)Menu item name
priceNUMERIC(10,2)Current item price
Tablesrestaurantsordersorder_itemsmenu_items
Interviewer

Whenever you're ready, walk me through your approach or go straight to the code. The requirements and the four tables are in the Question tab.

You need to log in / sign up to run or submit.
CodePostgreSQL
Sign up free to run your codeLog inLn 1
Run your query to see results here.