Top 3 restaurants in each city

HardWindow functionsFood delivery

The problem

Rank restaurants within their city by revenue: the sum of subtotal on delivered orders. Show the top 3 in each city.

Return city, restaurant_name, revenue and revenue_rank (1 = highest; tied restaurants share a rank, and all ranks up to 3 are kept).

Learn this in the course: Window functions (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: Did WELCOME50 bring customers back? →

Your SQL (PostgreSQL · runs in your browser)

SELECT 
FROM 
;

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

Expected output
city	restaurant_name	revenue	revenue_rank
Bengaluru	Napoli Pizza Co.	56445	1
Bengaluru	Fire Pizzeria	48835	2
Bengaluru	Slice Pies	41020	3
Chennai	Desi Zaika	23740	1
Chennai	Sprout Basket	17680	2
Chennai	Tandoor Rasoi	17225	3
Delhi	Punjabi Dhaba	54895	1
Delhi	Tandoor Zaika	54135	2
Delhi	Mithai Lane	52285	3
Hyderabad	Dum Degh	34355	1
Hyderabad	Napoli Pizza Co.	30330	2
Hyderabad	Dragon House	29740	3
Indore	Deccan Handi	15445	1
Indore	Amritsari Dhaba	12750	2
Indore	Bun Joint	11655	3
Jaipur	Dragon Bowl	24700	1
Jaipur	Amritsari Zaika	18685	2
Jaipur	Desi Zaika	17540	3
Mumbai	Fire Kitchen	58275	1
Mumbai	Wok Bowl	55545	2
Mumbai	Cocoa Studio	52225	3
Pune	Royal Tadka	32940	1
Pune	Coastal Dosa Corner	27135	2
Pune	Amritsari Zaika	23900	3

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.