Premium New Year Stays in Goa — SQL Basics Problem
The revenue team is planning a thank-you mailer for guests who booked a premium stay in Goa over the New Year.
- Difficulty: Easy
- Topics: Basics
- Dialect: MySQL
- Problem: #523
Problem statement
The revenue team is planning a thank-you mailer for guests who booked a premium stay in Goa over the New Year.
Return every booking that is confirmed, is at a hotel in Goa, has a nightly_rate of at least 8000 rupees, and checks in between 2024-12-24 and 2025-01-02 inclusive. Return the columns booking_id, guest_name and nightly_rate, in any order.
Tables
Table: HotelBooking
| Column | Type |
|---|---|
| booking_id | int |
| guest_name | varchar |
| city | varchar |
| check_in | date |
| nightly_rate | int |
| status | enum(confirmed, cancelled, no_show) |
Primary key: booking_id.
One row per room booking. nightly_rate is in rupees, before GST.
Examples
Example 1
HotelBooking
| booking_id | guest_name | city | check_in | nightly_rate | status |
|---|---|---|---|---|---|
| 1 | Aarav | Goa | 2024-12-28 | 9500 | confirmed |
| 2 | Diya | Goa | 2024-12-30 | 8000 | confirmed |
| 3 | Kabir | Jaipur | 2024-12-31 | 12000 | confirmed |
| 4 | Meera | Goa | 2024-12-26 | 15000 | cancelled |
| 5 | Rohan | Goa | 2025-01-03 | 11000 | confirmed |
| 6 | Zara | Goa | 2024-12-24 | 7500 | confirmed |
| 7 | Ishaan | Goa | 2025-01-02 | 13500 | confirmed |
Output
| booking_id | guest_name | nightly_rate |
|---|---|---|
| 1 | Aarav | 9500 |
| 2 | Diya | 8000 |
| 7 | Ishaan | 13500 |
How to solve Premium New Year Stays in Goa
This is a pure filter on one table: each booking either qualifies or it does not, and nothing has to be counted or combined. Four conditions must all be true, so they are joined with AND in the WHERE clause: the status is confirmed (cancelled bookings and no-shows are not guests to thank), the city is Goa, the nightly rate is at least 8000, and the check-in date falls in the holiday window.
Both boundaries are inclusive. nightly_rate >= 8000 keeps a booking at exactly 8000, which a strict > would lose, and BETWEEN '2024-12-24' AND '2025-01-02' keeps check-ins on both end dates. Dates stored as YYYY-MM-DD compare correctly as text and as dates, so the range test is safe.
The alternative spells the same range with >= and <= and writes the rate test as NOT nightly_rate < 8000. With an index on (city, check_in) the query reads only the Goa bookings in the window; without one it is a single scan.
Reference solution (MySQL)
SELECT booking_id, guest_name, nightly_rate
FROM HotelBooking
WHERE status = 'confirmed'
AND city = 'Goa'
AND nightly_rate >= 8000
AND check_in BETWEEN '2024-12-24' AND '2025-01-02'Another way
SELECT booking_id, guest_name, nightly_rate FROM HotelBooking WHERE status IN ('confirmed') AND city = 'Goa' AND NOT nightly_rate < 8000 AND check_in >= '2024-12-24' AND check_in <= '2025-01-02'← Electricity Bill Settlement From Part Payments · Room Nights Sold per Hotel →