Label Indore restaurants

EasyNULLs & CASECASEFood delivery

The problem

The Indore team wants every restaurant labelled for a campaign. Restaurants with cost_for_two under ₹400 are 'budget'; the rest are 'premium'. Veg-only restaurants are 'veg'; the rest are 'non-veg'. Indore is city_id 8.

Return name, cost_for_two and segment, where segment is one of 'veg budget', 'veg premium', 'non-veg budget' or 'non-veg premium'.

Learn this in the course: Aggregation and GROUP BY (video and notes)

Tables · ClearBite food delivery

ClearBite is a fictional food-delivery app in 8 Indian cities. The data covers calendar year 2025 and is invented.

Click a table or column name to insert it into your query. Open a table to see its columns.

9,975 rows · 11 columns

INTEGER · primary key
INTEGER · → customers
INTEGER · → restaurants
TIMESTAMP · local time
TEXT · delivered | cancelled | refunded
TEXT · upi | card | cod | wallet
TEXT · → coupons, NULL if none
INTEGER · rupees, food only
INTEGER · rupees
INTEGER · rupees
INTEGER · subtotal − discount + delivery_fee

Sample rows (9,975 total)

Sample rows from orders
order_id	customer_id	restaurant_id	ordered_at	status	payment_method	coupon_code	subtotal	delivery_fee	total
1	2126	115	2025-01-01 12:05:36	delivered	wallet	NULL	180	39	219
2	797	216	2025-01-01 12:52:20	cancelled	upi	NULL	150	41	191
3	1724	2	2025-01-01 12:53:25	delivered	upi	NULL	45	29	74
4	2135	59	2025-01-01 12:57:02	cancelled	upi	NULL	195	34	229
5	2201	191	2025-01-01 13:38:18	delivered	cod	NULL	95	0	95

16,314 rows · 4 columns

INTEGER · → orders
INTEGER · → menu_items
INTEGER
INTEGER · rupees

Sample rows (16,314 total)

Sample rows from order_items
order_id	item_id	quantity	unit_price
1	957	1	180
2	1790	1	150
3	18	1	45
4	487	1	195
5	1575	1	95

3,000 rows · 6 columns

INTEGER · primary key
TEXT · not unique
INTEGER · → cities
DATE
TEXT · organic | referral | paid_social | search_ads | offline, can be NULL
BOOLEAN · free delivery membership

Sample rows (3,000 total)

Sample rows from customers
customer_id	name	city_id	signup_date	acquisition_channel	is_premium
1	Bhavna Khan	6	2024-04-28	search_ads	false
2	Ayesha Joshi	3	2025-05-29	search_ads	false
3	Isha Nair	2	2025-12-03	offline	false
4	Pranav Ghosh	5	2025-11-06	NULL	false
5	Farhan Gowda	6	2025-06-28	paid_social	false

300 rows · 7 columns

INTEGER · primary key
TEXT · not unique
INTEGER · → cities
TEXT
BOOLEAN
INTEGER · rupees
DATE

Sample rows (300 total)

Sample rows from restaurants
restaurant_id	name	city_id	cuisine	is_veg_only	cost_for_two	onboarded_date
1	Slice Kitchen	5	Pizza	false	850	2023-10-18
2	Napoli Pizza Co.	1	Pizza	false	750	2023-09-18
3	Hakka Garden	8	Chinese	false	600	2022-02-08
4	Sprout Kitchen	2	Healthy	false	850	2025-09-28
5	Coastal Tiffins	6	South Indian	true	400	2025-07-27

2,482 rows · 6 columns

INTEGER · primary key
INTEGER · → restaurants
TEXT
TEXT · main | starter | bread | rice | dessert | beverage
BOOLEAN
INTEGER · rupees

Sample rows (2,482 total)

Sample rows from menu_items
item_id	restaurant_id	item_name	category	is_veg	price
1	1	Pasta Arrabbiata	main	true	250
2	1	Choco Lava Cake	dessert	true	120
3	1	BBQ Chicken Pizza	main	false	365
4	1	Cold Coffee	beverage	true	135
5	1	Farmhouse Pizza	main	true	350

9,173 rows · 6 columns

INTEGER · primary key, → orders (delivered and refunded orders only)
INTEGER · → delivery_partners
TIMESTAMP
TIMESTAMP
NUMERIC(4,1)
INTEGER · time promised at checkout, from ordered_at

Sample rows (9,173 total)

Sample rows from deliveries
order_id	partner_id	picked_up_at	delivered_at	distance_km	promised_minutes
1	182	2025-01-01 12:31:48	2025-01-01 12:46:29	4.8	35
3	200	2025-01-01 13:09:13	2025-01-01 13:12:55	2.2	30
5	218	2025-01-01 13:51:37	2025-01-01 14:15:14	2.8	30
7	108	2025-01-01 16:44:53	2025-01-01 16:49:29	2.3	25
8	100	2025-01-01 18:32:46	2025-01-01 18:36:45	1.1	20

4,181 rows · 4 columns

INTEGER · primary key, → orders
INTEGER · 1–5
INTEGER · 1–5, can be NULL
TIMESTAMP

Sample rows (4,181 total)

Sample rows from ratings
order_id	food_rating	delivery_rating	rated_at
3	4	NULL	2025-01-02 08:00:55
5	5	NULL	2025-01-01 21:55:14
7	4	5	2025-01-02 06:12:29
9	4	4	2025-01-03 02:11:41
10	3	4	2025-01-02 15:35:00

400 rows · 5 columns

INTEGER · primary key
TEXT
INTEGER · → cities
TEXT · bike | scooter | ev_scooter | bicycle
DATE

Sample rows (400 total)

Sample rows from delivery_partners
partner_id	name	city_id	vehicle_type	joined_date
1	Ananya Gowda	5	scooter	2023-01-08
2	Omkar Ghosh	3	bike	2024-08-21
3	Ayesha Qureshi	8	bike	2025-03-02
4	Vikram Krishnan	7	bike	2023-02-12
5	Varun Reddy	5	bike	2023-08-15

8 rows · 7 columns

TEXT · primary key
TEXT
INTEGER
INTEGER · rupees
INTEGER · rupees, on subtotal
DATE
DATE

Sample rows (8 total)

Sample rows from coupons
coupon_code	description	discount_pct	max_discount	min_order_value	valid_from	valid_to
WELCOME50	50% off your first order	50	100	199	2025-01-01	2025-12-31
BITE20	20% off any order	20	80	299	2025-01-01	2025-12-31
WEEKEND15	15% off on Saturdays and Sundays	15	75	249	2025-01-01	2025-12-31
IPL25	25% off during the cricket season	25	125	399	2025-03-22	2025-06-03
MONSOON10	10% off in July and August	10	60	199	2025-07-01	2025-08-31

8 rows · 4 columns

INTEGER · primary key
TEXT
TEXT
INTEGER · 1 = metro, 2 = smaller city

Sample rows (8 total)

Sample rows from cities
city_id	city	state	tier
1	Bengaluru	Karnataka	1
2	Mumbai	Maharashtra	1
3	Delhi	Delhi	1
4	Hyderabad	Telangana	1
5	Pune	Maharashtra	1

Next: Premium vs regular customers by city →

Your SQL (PostgreSQL · runs in your browser)

SELECT name, cost_for_two,
       CASE
         WHEN 
       END AS segment
FROM restaurants
WHERE city_id = 8;

Ctrl/⌘ + Enter runs the query. Table and column names are suggested as you type. Press Esc then Tab to leave the editor with the keyboard.

Expected output (21 rows)

Expected output
name	cost_for_two	segment
Hakka Garden	600	non-veg premium
Royal Tadka	550	non-veg premium
Chaat Adda	200	veg budget
Wok Bowl	550	non-veg premium
Bean Stop	350	non-veg budget
Brew Cafe	350	non-veg budget
Deccan Handi	500	non-veg premium
Amritsari Rasoi	350	veg budget
Bun Joint	500	non-veg premium
Wok Bowl	400	non-veg premium
Chai Cafe	550	non-veg premium
Green Table	650	non-veg premium
Tandoor Tadka	650	non-veg premium
Red Lantern House	400	non-veg premium
Coastal Dosa Corner	400	veg premium
Tapri Stall	150	veg budget
Bombay Bites	200	non-veg budget
Amritsari Dhaba	450	non-veg premium
Smash Bros	500	non-veg premium
Sugar Studio	350	veg budget
Tandoor Rasoi	650	non-veg premium

Row order doesn’t matter for this question. Numbers are compared with a tiny tolerance; text must match exactly.

Stuck?

Stored on this device only. Your latest query and “solved” status are remembered in this browser only. They don’t sync between devices, and clearing your browser data removes them.