Orders per Pune restaurant, including none

EasyJoinsLEFT JOINFood delivery

The problem

The Pune team (city_id 5) wants an order count for every Pune restaurant, including any restaurant that has had no orders yet.

Return restaurant_id, name and orders.

Learn this in the course: Joins and NULLs (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: What was ordered on New Year's Eve? →

Your SQL (PostgreSQL · runs in your browser)

SELECT r.restaurant_id, r.name, COUNT() AS orders
FROM restaurants r
LEFT JOIN orders o ON 
WHERE r.city_id = 5
GROUP BY r.restaurant_id, r.name;

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 (34 rows)

Expected output
restaurant_id	name	orders
1	Slice Kitchen	25
14	Slice Pizza Co.	41
16	Dragon Kitchen	48
17	Royal Kitchen	1
21	Bombay Wala	0
22	Tapri Adda	1
41	Bun Shack	33
47	Nawabi Handi	20
51	Rabri Ghar	40
52	Lucknowi Degh	36
58	Madras Mess	7
60	Bamboo House	14
91	Slice Pies	16
92	Coastal Dosa Corner	111
114	Sugar Lane	39
137	Crust Pizza Co.	24
150	Madras Cafe	24
158	Bun Joint	17
160	Red Lantern House	35
164	Crust Pizzeria	18
174	Dragon House	10
181	Red Lantern Bowl	21
200	Smash Burger Bar	29
205	Tapri Adda	48
212	Crust Pies	2
223	Amritsari Zaika	78
225	Cocoa Studio	11
229	Fresh Kitchen	35
230	Tandoor Kitchen	4
233	Green Basket	7
235	Smash Shack	4
252	Chaat Bites	33
279	Royal Tadka	79
296	Millet Kitchen	30

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.