Delivered and cancelled orders per restaurant

Easy

Question

A food delivery app wants one row per restaurant that shows how its orders ended.

Each order has a status: delivered, cancelled or pending.

Return restaurant (the name), delivered (number of delivered orders), cancelled (number of cancelled orders) and delivered_revenue (the sum of the amounts of the delivered orders).

A restaurant with no delivered order must show 0 revenue, not NULL. Every restaurant has at least one order. The order of the rows does not matter.

group by
conditional aggregation
case
sum
null

Read first: Lesson 45, SQL groups: GROUP BY, HAVING and conditional counts

Tables

2 tables

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

restaurants3 columns · 4 rowsnot run yet

CREATE TABLE restaurants (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);

Example rows of the restaurants table
id	name	city
1	Spice Route	Mumbai
2	Dosa Corner	Chennai
3	Tandoor House	Delhi
4	Biryani Bay	Hyderabad

food_orders5 columns · 12 rowsnot run yet

CREATE TABLE food_orders (id INTEGER PRIMARY KEY, restaurant_id INTEGER NOT NULL REFERENCES restaurants(id), status TEXT NOT NULL, amount INTEGER NOT NULL, ordered_at TEXT NOT NULL);

Example rows of the food_orders table
id	restaurant_id	status	amount	ordered_at
1	1	delivered	450	2026-03-01 12:10:00
2	1	delivered	380	2026-03-01 13:05:00
3	1	cancelled	520	2026-03-01 19:40:00
4	2	delivered	220	2026-03-02 08:15:00
5	2	pending	260	2026-03-02 09:00:00
6	2	delivered	190	2026-03-02 20:30:00
7	3	cancelled	700	2026-03-03 21:10:00
8	3	cancelled	640	2026-03-03 21:45:00
9	4	delivered	560	2026-03-04 13:20:00
10	4	delivered	610	2026-03-04 14:00:00
11	4	delivered	480	2026-03-04 20:15:00
12	4	cancelled	300	2026-03-05 12:30:00

About this SQL

Postgres has COUNT(*) FILTER (WHERE ...) and SQLite supports it from version 3.30, but SUM(CASE WHEN ...) works everywhere and is what interviewers expect. A comparison like status = 'delivered' is case-sensitive in SQLite for text.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready