ClearJob

Project 2 · Q8: Holiday customers

MediumJoinsProject 2Food delivery

The problem

The buyer wants one list of every customer who ordered on Christmas Day (25 December 2025) or on New Year's Eve (31 December 2025). Someone who ordered on both days should appear once.

Return customer_id and name.

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_idcustomer_idrestaurant_idordered_atstatuspayment_methodcoupon_codesubtotaldiscountdelivery_feetotal
121261152025-01-01 12:05:36deliveredwalletNULL180039219
27972162025-01-01 12:52:20cancelledupiNULL150041191
3172422025-01-01 12:53:25deliveredupiNULL4502974
42135592025-01-01 12:57:02cancelledupiNULL195034229
522011912025-01-01 13:38:18deliveredcodNULL950095
16,314 rows · 4 columns
  • INTEGER · → orders
  • INTEGER · → menu_items
  • INTEGER
  • INTEGER · rupees
Sample rows (16,314 total)
Sample rows from order_items
order_iditem_idquantityunit_price
19571180
217901150
318145
44871195
51575195
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_idnamecity_idsignup_dateacquisition_channelis_premium
1Bhavna Khan62024-04-28search_adsfalse
2Ayesha Joshi32025-05-29search_adsfalse
3Isha Nair22025-12-03offlinefalse
4Pranav Ghosh52025-11-06NULLfalse
5Farhan Gowda62025-06-28paid_socialfalse
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_idnamecity_idcuisineis_veg_onlycost_for_twoonboarded_date
1Slice Kitchen5Pizzafalse8502023-10-18
2Napoli Pizza Co.1Pizzafalse7502023-09-18
3Hakka Garden8Chinesefalse6002022-02-08
4Sprout Kitchen2Healthyfalse8502025-09-28
5Coastal Tiffins6South Indiantrue4002025-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_idrestaurant_iditem_namecategoryis_vegprice
11Pasta Arrabbiatamaintrue250
21Choco Lava Cakedesserttrue120
31BBQ Chicken Pizzamainfalse365
41Cold Coffeebeveragetrue135
51Farmhouse Pizzamaintrue350
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_idpartner_idpicked_up_atdelivered_atdistance_kmpromised_minutes
11822025-01-01 12:31:482025-01-01 12:46:294.835
32002025-01-01 13:09:132025-01-01 13:12:552.230
52182025-01-01 13:51:372025-01-01 14:15:142.830
71082025-01-01 16:44:532025-01-01 16:49:292.325
81002025-01-01 18:32:462025-01-01 18:36:451.120
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_idfood_ratingdelivery_ratingrated_at
34NULL2025-01-02 08:00:55
55NULL2025-01-01 21:55:14
7452025-01-02 06:12:29
9442025-01-03 02:11:41
10342025-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_idnamecity_idvehicle_typejoined_date
1Ananya Gowda5scooter2023-01-08
2Omkar Ghosh3bike2024-08-21
3Ayesha Qureshi8bike2025-03-02
4Vikram Krishnan7bike2023-02-12
5Varun Reddy5bike2023-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_codedescriptiondiscount_pctmax_discountmin_order_valuevalid_fromvalid_to
WELCOME5050% off your first order501001992025-01-012025-12-31
BITE2020% off any order20802992025-01-012025-12-31
WEEKEND1515% off on Saturdays and Sundays15752492025-01-012025-12-31
IPL2525% off during the cricket season251253992025-03-222025-06-03
MONSOON1010% off in July and August10601992025-07-012025-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_idcitystatetier
1BengaluruKarnataka1
2MumbaiMaharashtra1
3DelhiDelhi1
4HyderabadTelangana1
5PuneMaharashtra1

Next: Premium members by city →

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 (77 rows)
Expected output
customer_idname
153Deepa Subramanian
242Aditi D'Souza
305Deepa Agarwal
307Karan Mukherjee
337Uday Gupta
398Gaurav Yadav
463Omkar Ghosh
468Ayesha Agarwal
479Mohit Sethi
499Aditi Sandhu
514Kavya Rao
524Mary Gill
603Sahil Pandey
611Varun Gill
632Divya Mehta
691Varun Kulkarni
725Isha Patel
767Gaurav Gowda
872Jasleen Sharma
887Mary Dutta
911Lalit Sandhu
921Nikhil Gupta
973Ayesha Saxena
991Aarav Patel
1024Ishaan Shah
1031Nandini Sethi
1078Aarav Gupta
1089Naveen D'Souza
1110Simran Patel
1119Gurpreet Gupta
1128Arjun Tiwari
1152Akash Gowda
1203Sandeep Subramanian
1241Varun Agarwal
1255Anjali Jain
1318Rohan Hegde
1335Deepa Ghosh
1364Karan Khan
1370Kiran Dutta
1398Divya Sharma
1433Sandeep Kulkarni
1448Divya Tiwari
1474Karan Banerjee
1573Gurpreet Mehta
1578Isha Malhotra
1591Vikram Khan
1605Isha Hegde
1662Anjali Shetty
1728Simran Deshpande
1754Bhavna Sethi
1781Uday Dutta
1817Ishaan Qureshi
1869Suresh Agarwal
1882Lalit D'Souza
1900Uday Menon
1930Omkar D'Souza
2009Gurpreet Ghosh
2076Fatima Kamath
2079Dev Kamath
2082Mary Subramanian
2084Ayesha Gowda
2105Harini Krishnan
2116Sana Shetty
2138Priya Subramanian
2228Manish Fernandes
2242Abhishek Rao
2269Kavya Kapoor
2272Akash Hegde
2435Yash Gupta
2681Shreya Mukherjee
2740Simran Bhat
2754Chirag Dutta
2905Vidya Pillai
2937Jasleen Gowda
2946Uday Khan
2957Gurpreet Sharma
2971Nikhil Bose

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.