Recent Orders
List orders placed after January 20, 2024 — show the order ID, customer full name, and order date.
- Joins
- Date analysis
- Filtering
- Sorting
Challenge brief
Understand the request
Customer Service needs to pull all orders placed after January 20 to investigate a shipping delay report.
Show orders placed after January 20, 2024 with customer full name and order date.
Return
- order_id
- customer_name (first_name + space + last_name)
- order_date
Constraints
- The reporting window is strictly after January 20, 2024
- Customer names must contain first and last name separated by one space
- Return orders from earliest to latest within the window
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
orders
order_idINTEGERcustomer_idINTEGERorder_dateDATETIME
customers
customer_idINTEGERfirst_nameVARCHAR(50)last_nameVARCHAR(50)
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Order data is in orders, but customer names are in customers. Connect them using the customer_id column that appears in both tables. You also need to combine first_name and last_name into one column.
Hint 2
Use INNER JOIN orders to customers on customer_id. Filter with WHERE order_date > '2024-01-20'. Concatenate names in SQLite using the || operator: first_name || ' ' || last_name AS customer_name.
Hint 3
Scaffold: join orders to customers, build customer_name, apply the strict lower date boundary, and finish with ascending order_date.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT o.order_id, c.first_name || ' ' || c.last_name AS customer_name, o.order_date FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_date > '2024-01-20' ORDER BY o.order_date;Why this works
The || operator concatenates strings in SQLite. Date comparison works on ISO-format strings (YYYY-MM-DD HH:MM:SS) because they sort correctly as text. 8 of the 10 orders fall after January 20.
Success check
8 orders placed after January 20, 2024
Expected result
Use this output to verify values, aliases, ordering, and row count.
| order_id | customer_name | order_date |
|---|---|---|
| 5003 | Alice Johnson | 2024-01-20 09:15:00 |
| 5004 | Carol White | 2024-01-22 16:45:00 |
| 5005 | David Brown | 2024-01-25 11:00:00 |
| 5006 | Bob Smith | 2024-02-01 13:30:00 |
| 5007 | Emma Davis | 2024-02-03 10:20:00 |
| 5008 | Frank Miller | 2024-02-05 15:10:00 |
| 5009 | Alice Johnson | 2024-02-10 14:00:00 |
| 5010 | Bob Smith | 2024-02-15 11:30:00 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Explore related company challenges
Airbnb
Independent Airbnb-style marketplace, booking, listing, payment, review, and guest analytics SQL practice.
Uber
Independent Uber-style mobility marketplace SQL practice covering trips, drivers, riders, pricing, payments, and promotions.
Microsoft
Independent Microsoft-style cloud, productivity, subscription, usage, support, and customer analytics SQL practice.
Return to the complete interview preparation experience.