Premium New Year Stays in Goa

Easy
523.

Premium New Year Stays in Goa

EasyBasics

MySQL

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

ColumnTypeValues
booking_idint
guest_namevarchar
cityvarchar
check_indate
nightly_rateint
statusenumconfirmed, cancelled, no_show

Primary key: booking_id.

One row per room booking. nightly_rate is in rupees, before GST.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

HotelBooking

booking_idguest_namecitycheck_innightly_ratestatus
1AaravGoa2024-12-289500confirmed
2DiyaGoa2024-12-308000confirmed
3KabirJaipur2024-12-3112000confirmed
4MeeraGoa2024-12-2615000cancelled
5RohanGoa2025-01-0311000confirmed
6ZaraGoa2024-12-247500confirmed
7IshaanGoa2025-01-0213500confirmed

Output

booking_idguest_namenightly_rate
1Aarav9500
2Diya8000
7Ishaan13500
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
booking_idint
guest_namevarchar
cityvarchar
check_indate
nightly_rateint
statusenum(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_idguest_namecitycheck_innightly_ratestatus
1AaravGoa2024-12-289500confirmed
2DiyaGoa2024-12-308000confirmed
3KabirJaipur2024-12-3112000confirmed
4MeeraGoa2024-12-2615000cancelled
5RohanGoa2025-01-0311000confirmed
6ZaraGoa2024-12-247500confirmed
7IshaanGoa2025-01-0213500confirmed

Output

booking_idguest_namenightly_rate
1Aarav9500
2Diya8000
7Ishaan13500

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 →