Core Entities
Hotel: hotel_id, name, address, star_rating, amenities (JSON array), check_in_time, check_out_time. RoomType: type_id, hotel_id, name (STANDARD, DELUXE, SUITE, PENTHOUSE), capacity (max guests), base_price, amenities. Room: room_id, hotel_id, type_id, number, floor, status (AVAILABLE, OCCUPIED, MAINTENANCE, HOUSEKEEPING), is_active. Booking: booking_id, hotel_id, guest_id, room_id (nullable until check-in), type_id, check_in_date, check_out_date, status (CONFIRMED, CHECKED_IN, CHECKED_OUT, CANCELLED, NO_SHOW), adults, children, total_amount, paid_amount, booking_source (DIRECT, OTA, WALK_IN), created_at. Guest: guest_id, first_name, last_name, email, phone, id_type, id_number, nationality, loyalty_tier (NONE, SILVER, GOLD, PLATINUM). Invoice: invoice_id, booking_id, line_items (JSON: [{desc, qty, rate, amount}]), subtotal, tax, total, issued_at.
Room Availability and Booking
class BookingService:
def get_available_rooms(self, hotel_id: int, check_in: date,
check_out: date, type_id: int,
adults: int) -> list[Room]:
# Find rooms of requested type not booked in the date range
booked_room_ids = self.db.query("""
SELECT DISTINCT room_id FROM bookings
WHERE hotel_id = :h AND room_id IS NOT NULL
AND status IN ('CONFIRMED', 'CHECKED_IN')
AND check_in_date :in
""", h=hotel_id, out=check_out, in=check_in)
return self.db.query("""
SELECT r.* FROM rooms r
JOIN room_types rt ON r.type_id = rt.type_id
WHERE r.hotel_id = :h AND r.type_id = :t
AND r.status = 'AVAILABLE' AND r.is_active = TRUE
AND r.room_id NOT IN :booked
AND rt.capacity >= :adults
""", h=hotel_id, t=type_id, booked=booked_room_ids, adults=adults)
def create_booking(self, request: BookingRequest) -> Booking:
with self.db.transaction():
available = self.get_available_rooms(
request.hotel_id, request.check_in,
request.check_out, request.type_id, request.adults
)
if not available:
raise NoRoomsAvailableError()
booking = Booking(
hotel_id=request.hotel_id,
guest_id=request.guest_id,
type_id=request.type_id,
check_in_date=request.check_in,
check_out_date=request.check_out,
status=BookingStatus.CONFIRMED,
total_amount=self._calculate_total(request)
)
self.repo.save(booking)
return bookingDynamic Pricing (Revenue Management)
Hotel room prices are not static — they vary by occupancy, season, day of week, and booking lead time. Pricing factors: base_price (from RoomType), occupancy multiplier: if >80% of rooms are booked for a night: apply a 1.2x multiplier. If <30%: apply a 0.9x discount. Day-of-week: weekends are 1.15x on leisure hotels, weekdays 1.15x on business hotels. Lead time: booking 90+ days in advance: 0.9x. Booking within 3 days: 1.3x (last-minute premium). Events: if a major conference is in the city on those dates: 1.5x. Pricing tiers are stored in a PricingRule table and evaluated at booking time. The calculated price is locked in at booking time and stored on the Booking — future pricing changes don't affect confirmed bookings.
Check-In, Housekeeping, and Billing
Check-in flow: (1) Guest arrives, receptionist looks up the booking by booking_id or guest name. (2) Verify guest ID (id_type, id_number). (3) Assign a specific room: pick from available AVAILABLE rooms of the booked type (prefer floors the guest has stayed on before, prefer non-adjacent to noisy rooms if available). (4) Update: booking.room_id = assigned_room, booking.status = CHECKED_IN, room.status = OCCUPIED. Housekeeping state machine: OCCUPIED → HOUSEKEEPING (after check-out or daily service request) → AVAILABLE (after housekeeping complete). The housekeeping app allows staff to mark rooms as cleaned. Room is not reassignable until status = AVAILABLE. Check-out and billing: (1) Retrieve all charges: base room rate (nightly * nights_stayed), additional charges (minibar, room service, spa — added via the folio system during stay). (2) Apply loyalty discounts (GOLD: 5% off, PLATINUM: 10% off). (3) Generate invoice. (4) Process payment (charge to card on file). (5) Update room status to HOUSEKEEPING. Email invoice to guest. Folio charges: during the stay, any service charge (restaurant, spa) is added to the booking’s folio (PostCharge: charge_id, booking_id, description, amount, posted_at, posted_by).
Asked at: Airbnb Interview Guide
Asked at: Stripe Interview Guide
Asked at: Shopify Interview Guide
Asked at: Uber Interview Guide