Pure-Veg Restaurants Rated 4.2 or Higher

Easy
148.

Pure-Veg Restaurants Rated 4.2 or Higher

EasyBasics

MySQL

The app's "Pure Veg" collection page lists restaurants that serve no meat or egg, are currently accepting orders, and hold an average rating of at least 4.2. A restaurant that has just joined has no rating yet (avg_rating NULL) and is never listed.

Return restaurant_id, name and avg_rating for every restaurant that qualifies, ordered by avg_rating descending, then by restaurant_id ascending.

Tables

Table: Restaurant

ColumnType
restaurant_idint
namevarchar
cityvarchar
is_pure_vegbool
is_accepting_ordersbool
avg_ratingdecimal

Primary key: restaurant_id.

One row per restaurant on the app. avg_rating is on a 1–5 scale with one decimal, NULL until the first rating.

Example

The rows must come back in the order shown.

Restaurant

restaurant_idnamecityis_pure_vegis_accepting_ordersavg_rating
1Udupi UpaharBengaluru114.5
2Kebab KothiDelhi014.6
3Dosa JunctionChennai114.2
4Thali TimesAhmedabad104.8
5Green BowlPune11NULL
6Idli FactoryBengaluru114.1
7Chaat CornerJaipur114.5

Output

restaurant_idnameavg_rating
1Udupi Upahar4.5
7Chaat Corner4.5
3Dosa Junction4.2
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Pure-Veg Restaurants Rated 4.2 or Higher — SQL Problem

The app's "Pure Veg" collection page lists restaurants that serve no meat or egg, are currently accepting orders, and hold an average rating of at least 4.2.

  • Difficulty: Easy
  • Topics: Basics
  • Dialect: MySQL
  • Problem: #148

Problem statement

The app's "Pure Veg" collection page lists restaurants that serve no meat or egg, are currently accepting orders, and hold an average rating of at least 4.2. A restaurant that has just joined has no rating yet (avg_rating NULL) and is never listed.

Return restaurant_id, name and avg_rating for every restaurant that qualifies, ordered by avg_rating descending, then by restaurant_id ascending.

Tables

Table: Restaurant

ColumnType
restaurant_idint
namevarchar
cityvarchar
is_pure_vegbool
is_accepting_ordersbool
avg_ratingdecimal

Primary key: restaurant_id.

One row per restaurant on the app. avg_rating is on a 1–5 scale with one decimal, NULL until the first rating.

Examples

Example 1

Restaurant

restaurant_idnamecityis_pure_vegis_accepting_ordersavg_rating
1Udupi UpaharBengaluru114.5
2Kebab KothiDelhi014.6
3Dosa JunctionChennai114.2
4Thali TimesAhmedabad104.8
5Green BowlPune11NULL
6Idli FactoryBengaluru114.1
7Chaat CornerJaipur114.5

Output

restaurant_idnameavg_rating
1Udupi Upahar4.5
7Chaat Corner4.5
3Dosa Junction4.2

How to solve Pure-Veg Restaurants Rated 4.2 or Higher

This is a plain filter over one table. A restaurant belongs on the page when three things are all true: it is pure veg, it is accepting orders right now, and its average rating is at least 4.2. Each is one comparison, and AND combines them.

"At least" means >=, so a restaurant sitting exactly at 4.2 is listed. A restaurant with no rating has avg_rating NULL; NULL >= 4.2 is unknown, and WHERE keeps only rows whose condition is true, so new restaurants drop out without an extra IS NOT NULL — the same holds for NOT (avg_rating < 4.2), because NOT of unknown is still unknown.

The order is fixed by the statement: highest rating first, and since ratings repeat, restaurant_id breaks the tie. Without the second key two restaurants rated 4.5 could come back in either order. The query reads the table once; an index on (is_pure_veg, is_accepting_orders, avg_rating) would serve it directly.

Reference solution (MySQL)

SELECT restaurant_id, name, avg_rating
FROM Restaurant
WHERE is_pure_veg = 1
  AND is_accepting_orders = 1
  AND avg_rating >= 4.2
ORDER BY avg_rating DESC, restaurant_id

Another way

SELECT restaurant_id, name, avg_rating FROM Restaurant WHERE is_pure_veg AND is_accepting_orders AND NOT (avg_rating < 4.2) ORDER BY avg_rating DESC, restaurant_id ASC

Another way

SELECT restaurant_id, name, avg_rating FROM Restaurant WHERE is_pure_veg + is_accepting_orders = 2 AND avg_rating BETWEEN 4.2 AND 5 ORDER BY 3 DESC, 1

← Day Each Branch Crossed 50% Placement · Payable Amount on Surge-Priced Orders →