What was ordered on New Year's Eve?

MediumJoinsBridge tablesFood delivery

The problem

The menu team wants every dish ordered on New Year's Eve (31 December 2025), one row per dish in each order.

Return order_id, item_name and quantity.

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: Desserts on sale in Indore →

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

Expected output
order_id	item_name	quantity
9933	Double Cheese Burger	1
9934	Pasta Arrabbiata	1
9935	Kulfi Falooda	1
9935	Gulab Jamun	1
9935	Rasmalai	1
9936	Chicken Tikka	1
9937	Phirni	1
9938	Margherita Pizza	1
9939	Mirchi Ka Salan	1
9940	Chole Bhature	3
9941	Misal Pav	1
9941	Pav Bhaji	1
9942	Rabri Jalebi	1
9943	Croissant	1
9944	Tandoori Roti	2
9945	Butter Naan	1
9945	Butter Chicken	1
9946	Mirchi Ka Salan	1
9947	Appam with Stew	1
9947	Medu Vada	1
9948	Crispy Chicken Wrap	1
9948	Chicken Burger	1
9948	Double Cheese Burger	2
9948	Paneer Burger	1
9949	Pav Bhaji	2
9950	Chole Bhature	1
9950	Mutton Rogan Josh	1
9950	Dal Makhani	1
9951	Garlic Bread	2
9951	Pasta Arrabbiata	1
9952	Raita	1
9952	Veg Biryani	1
9953	Paneer Protein Bowl	1
9954	Veg Biryani	2
9954	Paneer Biryani	1
9955	Rava Dosa	2
9956	Butter Naan	1
9957	Cold-Pressed Juice	1
9958	Quinoa Salad Bowl	1
9959	Veg Club Sandwich	1
9960	Aloo Tikki Burger	1
9960	Chicken Nuggets	1
9961	Butter Naan	2
9962	Dal Makhani	1
9962	Sweet Lassi	1
9963	Kulfi Falooda	1
9963	Rabri Jalebi	1
9964	Rabri Jalebi	2
9964	Kulfi Falooda	2
9965	Chicken Tikka	1
9966	Pav Bhaji	1
9967	Quinoa Salad Bowl	1
9968	Brownie Sundae	1
9968	Rasmalai	2
9969	Paneer Biryani	1
9970	Fish Fry	1
9970	Idli Vada	2
9971	Sweet Lassi	1
9972	Rasmalai	1
9973	Paneer Tikka Pizza	2
9974	Paneer Tikka	3
9975	Paneer Kathi Roll	1

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.