Zomato: A User's Orders from the Last 30 Days

SQL coding challenge · Difficulty: easy · Topic: Date Functions · +50 XP

Problem

*Asked in a Zomato data interview.*

Zomato's app shows each user their recent orders. Write a query that returns every order placed by user U123 in the last 30 days, counting back from the reference date 2025-01-30.

Only orders whose user_id is exactly U123 count. Other users' orders are in the same table.
The window is inclusive at both ends: from 2024-12-31 (exactly 30 days before 2025-01-30) up to and including 2025-01-30.
Orders older than that, and orders dated after 2025-01-30 (pre-booked orders), are not returned.
Every order in the window is returned, even when the user placed several on the same day.
Use the fixed reference date 2025-01-30, not today's date.

Return order_id, user_id, dish_name, order_date and amount. Sort by order_date from newest to oldest, then by order_id ascending (the grader checks the rows, not their order).

If U123 has no order in the window, the result is empty.

Schema

Table: orders

| Column | Type | Meaning |
| --- | --- | --- |
| order_id | INT | Unique id of the order |
| user_id | VARCHAR | The user who placed the order |
| dish_name | VARCHAR | The dish ordered |
| order_date | DATE | The day the order is for |
| amount | DECIMAL(10,2) | Total value of the order, in rupees |

Tables

Table: orders

| order_id | user_id | dish_name | order_date | amount |
| --- | --- | --- | --- | --- |
| 1 | U123 | Burger | 2025-01-01 | 200.00 |
| 2 | U123 | Pasta | 2024-12-20 | 150.00 |
| 3 | U124 | Pizza | 2025-01-10 | 300.00 |
| 4 | U123 | Salad | 2024-12-30 | 100.00 |
| 5 | U124 | Burger | 2025-01-15 | 250.00 |
| 6 | U123 | Biryani | 2025-01-25 | 350.00 |
| 7 | U123 | Paneer Wrap | 2024-12-31 | 180.00 |
| 8 | U123 | Masala Dosa | 2025-01-25 | 120.00 |
| 9 | U123 | Thali | 2025-02-02 | 400.00 |
| 10 | U123 | Lassi | 2025-01-30 | 60.00 |

Expected Output

| order_id | user_id | dish_name | order_date | amount |
| --- | --- | --- | --- | --- |
| 10 | U123 | Lassi | 2025-01-30 | 60.00 |
| 6 | U123 | Biryani | 2025-01-25 | 350.00 |
| 8 | U123 | Masala Dosa | 2025-01-25 | 120.00 |
| 1 | U123 | Burger | 2025-01-01 | 200.00 |
| 7 | U123 | Paneer Wrap | 2024-12-31 | 180.00 |

Explanation

Order 10 is on the reference date 2025-01-30 itself, so it is in the window.
Orders 6 and 8 were both placed on 2025-01-25; both are returned, order 6 first.
Order 1 (2025-01-01) is inside the window.
Order 7 (2024-12-31) is exactly 30 days before 2025-01-30: the first day of the window, so it is included.
Order 4 (2024-12-30) is 31 days old and order 2 (2024-12-20) is older still: both are left out.
Order 9 is pre-booked for 2025-02-02, after the reference date: left out.
Orders 3 and 5 are recent, but they belong to U124.

Notes

Column names must be exactly order_id, user_id, dish_name, order_date and amount.
order_date is a DATE (no time of day), so a day is either in the window or not.
The query runs on MySQL 8.

What this SQL challenge teaches you

“Zomato: A User's Orders from the Last 30 Days” is a easy-level SQL challenge focused on Date Functions. Working through it gives you hands-on practice with WHERE, DATE_SUB, INTERVAL, BETWEEN, Date filtering, ORDER BY — the kind of transformation you are asked to write in real data engineering work and in technical interviews. You can solve it directly in the browser: the dataset is pre-loaded, so you write the query or DataFrame code, run it, and compare your output against the expected result immediately.

Concepts covered

WHERE
DATE_SUB
INTERVAL
BETWEEN
Date filtering
ORDER BY
Food Delivery

How to approach it

If you get stuck, work through these steps in order before looking at a full solution — each one narrows the problem down:

Start with one user: WHERE user_id = 'U123'. An exact match, not LIKE -- U1234 is a different user.
The window's first day is DATE_SUB('2025-01-30', INTERVAL 30 DAY), which is 2024-12-31. Use the fixed date 2025-01-30, not CURDATE().
Both ends are included. BETWEEN low AND high includes both, so it fits; with > and < you would lose the edges.
Don't forget the upper end: an order dated after 2025-01-30 is pre-booked and must not be returned.

Where this comes up

Variations of this problem have been reported in interviews at Zomato. Interviewers use it to check whether you can express the logic cleanly and reason about correctness on edge cases such as ties, nulls and empty groups.

How to practise it on PySpark.in

Open the challenge, write your SQL query in the editor and press Run to execute it against the sample dataset. Submitting checks your output against every test case, including hidden ones, so you find out straight away whether your logic holds up. You can retry as often as you like, and each solved challenge adds to your XP.

Related SQL challenges

Helpful resources

Frequently asked questions

Do I need to install Spark or a database to solve this?

No. The SQL environment runs in your browser with the sample data already loaded, so there is nothing to install or configure.

Is this challenge free?

Yes - the problem, the sample dataset, the hints and unlimited test runs are free.

What level is it?

It is rated easy and covers Date Functions.

Solve this challenge free on PySpark.in