Hotel Cancellation Refunds Under a Tiered Policy

Medium
536.

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 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

ColumnType
hotel_idint
hotel_namevarchar

Primary key: hotel_id.

One row per hotel.

Table: Booking

ColumnType
booking_idint
hotel_idint
check_indate
amount_paidint
cancelled_ondate

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.

Example

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

Hotel

hotel_idhotel_name
1Marine Drive Suites
2Hilltop Heritage
3Tea Garden Inn

Booking

booking_idhotel_idcheck_inamount_paidcancelled_on
112024-05-20120002024-05-10
212024-05-2090002024-05-14
312024-05-2280002024-05-21
412024-05-2515000NULL
522024-06-0164002024-05-30
622024-06-0370002024-06-04
732024-06-105000NULL

Output

hotel_namecancellationstotal_refund
Marine Drive Suites316500
Hilltop Heritage23200
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.

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

ColumnType
hotel_idint
hotel_namevarchar

Primary key: hotel_id.

One row per hotel.

Table: Booking

ColumnType
booking_idint
hotel_idint
check_indate
amount_paidint
cancelled_ondate

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_idhotel_name
1Marine Drive Suites
2Hilltop Heritage
3Tea Garden Inn

Booking

booking_idhotel_idcheck_inamount_paidcancelled_on
112024-05-20120002024-05-10
212024-05-2090002024-05-14
312024-05-2280002024-05-21
412024-05-2515000NULL
522024-06-0164002024-05-30
622024-06-0370002024-06-04
732024-06-105000NULL

Output

hotel_namecancellationstotal_refund
Marine Drive Suites316500
Hilltop Heritage23200

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_name

Another 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 →