Hotel Cancellation Refunds Under a Tiered Policy
The chain's cancellation policy refunds by how many days before check-in a booking was cancelled, DATEDIFF(check_in, cancelled_on): 7 days or more — a full…
- Difficulty: Medium
- Topics: Conditional Logic, Dates, Aggregation
- Dialect: MySQL
- Problem: #536
Problem statement
The chain's cancellation policy refunds by how many days before check-in a booking was cancelled, DATEDIFF(check_in, cancelled_on): 7 days or more — a full refund of amount_paid; 2 to 6 days — half of it; fewer than 2 days (including a cancellation on or after the check-in day) — nothing.
For every hotel with at least one cancelled booking, return hotel_name, cancellations (the number of cancelled bookings) and total_refund (the rupees refunded across them). Bookings that were not cancelled have cancelled_on NULL and are ignored. Return the rows in any order.
Tables
Table: Hotel
| Column | Type |
|---|---|
| hotel_id | int |
| hotel_name | varchar |
Primary key: hotel_id.
One row per hotel.
Table: Booking
| Column | Type |
|---|---|
| booking_id | int |
| hotel_id | int |
| check_in | date |
| amount_paid | int |
| cancelled_on | date |
Primary key: booking_id.
amount_paid is the prepaid amount in rupees (always even). cancelled_on is the date of cancellation, or NULL if the booking stands.
Examples
Example 1
Hotel
| hotel_id | hotel_name |
|---|---|
| 1 | Marine Drive Suites |
| 2 | Hilltop Heritage |
| 3 | Tea Garden Inn |
Booking
| booking_id | hotel_id | check_in | amount_paid | cancelled_on |
|---|---|---|---|---|
| 1 | 1 | 2024-05-20 | 12000 | 2024-05-10 |
| 2 | 1 | 2024-05-20 | 9000 | 2024-05-14 |
| 3 | 1 | 2024-05-22 | 8000 | 2024-05-21 |
| 4 | 1 | 2024-05-25 | 15000 | NULL |
| 5 | 2 | 2024-06-01 | 6400 | 2024-05-30 |
| 6 | 2 | 2024-06-03 | 7000 | 2024-06-04 |
| 7 | 3 | 2024-06-10 | 5000 | NULL |
Output
| hotel_name | cancellations | total_refund |
|---|---|---|
| Marine Drive Suites | 3 | 16500 |
| Hilltop Heritage | 2 | 3200 |
How to solve Hotel Cancellation Refunds Under a Tiered Policy
Each cancelled booking gets a refund decided by its notice period, DATEDIFF(check_in, cancelled_on) — the number of days between cancelling and the planned arrival. A CASE tested from the most generous band down maps that to an amount: >= 7 refunds everything, otherwise >= 2 refunds half, otherwise nothing. The bands meet at exact values (a notice of 7 is a full refund, 2 is half, 1 is nothing), and a cancellation on or after the check-in day has a notice of 0 or less, which falls into the last branch.
Bookings that stand have cancelled_on NULL. Filtering them out in WHERE leaves only cancellations, so COUNT(*) per hotel counts them and the SUM of the CASE totals the refunds; a hotel with no cancellation forms no group and is absent. amount_paid is even, so half of it is a whole number of rupees.
The alternative keeps every booking in the join, multiplies the amount by a 1 / 0.5 / 0 factor — for a NULL cancelled_on the DATEDIFF is NULL, the product NULL, and SUM skips it — counts cancelled_on (which ignores NULLs) and drops hotels without cancellations in HAVING.
Reference solution (MySQL)
SELECT h.hotel_name,
COUNT(*) AS cancellations,
SUM(CASE
WHEN DATEDIFF(b.check_in, b.cancelled_on) >= 7 THEN b.amount_paid
WHEN DATEDIFF(b.check_in, b.cancelled_on) >= 2 THEN b.amount_paid / 2
ELSE 0
END) AS total_refund
FROM Booking b
JOIN Hotel h ON h.hotel_id = b.hotel_id
WHERE b.cancelled_on IS NOT NULL
GROUP BY h.hotel_id, h.hotel_nameAnother way
SELECT h.hotel_name, COUNT(b.cancelled_on) AS cancellations,
SUM(b.amount_paid * IF(DATEDIFF(b.check_in, b.cancelled_on) >= 7, 1, IF(DATEDIFF(b.check_in, b.cancelled_on) >= 2, 0.5, 0))) AS total_refund
FROM Hotel h JOIN Booking b ON b.hotel_id = h.hotel_id
GROUP BY h.hotel_id, h.hotel_name
HAVING COUNT(b.cancelled_on) > 0← Top Two Agencies by Commission Each Quarter · Passengers Who Flew a Route in Both Directions →