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
| Column | Type |
|---|---|
| restaurant_id | int |
| name | varchar |
| city | varchar |
| is_pure_veg | bool |
| is_accepting_orders | bool |
| avg_rating | decimal |
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_id | name | city | is_pure_veg | is_accepting_orders | avg_rating |
|---|---|---|---|---|---|
| 1 | Udupi Upahar | Bengaluru | 1 | 1 | 4.5 |
| 2 | Kebab Kothi | Delhi | 0 | 1 | 4.6 |
| 3 | Dosa Junction | Chennai | 1 | 1 | 4.2 |
| 4 | Thali Times | Ahmedabad | 1 | 0 | 4.8 |
| 5 | Green Bowl | Pune | 1 | 1 | NULL |
| 6 | Idli Factory | Bengaluru | 1 | 1 | 4.1 |
| 7 | Chaat Corner | Jaipur | 1 | 1 | 4.5 |
Output
| restaurant_id | name | avg_rating |
|---|---|---|
| 1 | Udupi Upahar | 4.5 |
| 7 | Chaat Corner | 4.5 |
| 3 | Dosa Junction | 4.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_idAnother 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 ASCAnother 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 →