Core Entities
Hotel: hotel_id, name, address, city, star_rating, amenities (JSON), check_in_time, check_out_time. RoomType: room_type_id, hotel_id, name (STANDARD, DELUXE, SUITE), max_occupancy, base_price_per_night, amenities (JSON), total_rooms (total physical rooms of this type). Room: room_id, hotel_id, room_type_id, floor, room_number, status (AVAILABLE, UNDER_MAINTENANCE). Reservation: reservation_id, guest_id, hotel_id, room_type_id, room_id (assigned at check-in, null at booking), check_in_date, check_out_date, num_guests, status (PENDING, CONFIRMED, CHECKED_IN, CHECKED_OUT, CANCELLED), total_price, booked_at. PriceRule: rule_id, hotel_id, room_type_id, start_date, end_date, price_per_night (overrides base price for peak seasons).
Availability Check
The core query: how many rooms of type T are available at hotel H for dates [check_in, check_out)? Available = total_rooms – reserved_count. Reserved count: SELECT COUNT(*) FROM reservations WHERE hotel_id = H AND room_type_id = T AND status IN (‘CONFIRMED’, ‘CHECKED_IN’, ‘PENDING’) AND check_in_date :checkin. The date overlap condition: two reservations overlap if and only if one starts before the other ends AND ends after the other starts. Index on (hotel_id, room_type_id, check_in_date, check_out_date, status) for this query. Available rooms = total_rooms – reserved_count. If available > 0, booking is possible.
Reservation Flow with Concurrency
Race condition: two users book the last room simultaneously. Both check availability (count = 1), both proceed to insert. Both reservations are created — overbooking. Solution: use a database constraint + conditional insert. Option 1 — Optimistic locking: include a version check. Track a room_type_availability table with (hotel_id, room_type_id, date, available_count). On booking: UPDATE room_type_availability SET available_count = available_count – 1 WHERE hotel_id=H AND room_type_id=T AND date BETWEEN check_in AND check_out AND available_count > 0. If rows_affected total_rooms: rollback and return an error. Wrap in a database transaction with SERIALIZABLE isolation level.
Dynamic Pricing
Room prices vary by season, day of week, and demand. Price for a reservation = sum of nightly prices over all nights in [check_in, check_out). For each night: find the applicable PriceRule (hotel_id, room_type_id, date range). If a rule covers this date, use its price. Else use the base price. PriceRule table has (start_date, end_date) ranges. To find the applicable rule for a specific date: SELECT price_per_night FROM price_rules WHERE hotel_id=H AND room_type_id=T AND start_date = :date ORDER BY start_date DESC LIMIT 1 (most specific rule wins). At booking time: compute the total price for all nights, store it on the reservation. Price is locked at booking time (changes after booking don’t affect existing reservations).
Room Assignment at Check-In
At booking time, a room type is reserved (not a specific room). At check-in time, a specific room is assigned. This maximizes flexibility: the hotel can assign rooms based on current occupancy patterns, maintenance schedules, and guest preferences (floor, view, accessibility). Assignment logic: SELECT room_id FROM rooms WHERE hotel_id=H AND room_type_id=T AND status=AVAILABLE AND room_id NOT IN (SELECT room_id FROM reservations WHERE hotel_id=H AND check_in_date=:today AND status=CHECKED_IN) LIMIT 1 FOR UPDATE. UPDATE reservations SET room_id=:assigned_room, status=CHECKED_IN WHERE reservation_id=:id. The FOR UPDATE lock prevents two front-desk agents from assigning the same room simultaneously.
Cancellation and Refund Policy
CancellationPolicy (per hotel, per room type): free cancellation until N days before check-in. 50% refund if cancelled within N days. No refund if cancelled within 24 hours or after check-in. On cancellation: compute the refund amount based on the policy and the number of days until check-in. Create a Refund record. Mark the reservation as CANCELLED. Restore availability: decrement the reserved count (release the slot back to inventory). For the refund: trigger the payment gateway refund (see lld-payment-gateway). Important: the availability restoration and the status update must happen atomically (single transaction). Otherwise a crash between the two steps leaves the inventory inconsistent.
{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “How do you check room availability for a date range without scanning all reservations?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Index the reservations table on (hotel_id, room_type_id, check_in_date, check_out_date). The date overlap condition is: reservation.check_in_date query.check_in. This filters to only overlapping reservations. Count them and compare to total_rooms. For high-traffic hotels: maintain a pre-aggregated room_availability table with one row per (hotel_id, room_type_id, date) containing rooms_available. Update this table atomically when reservations are created or cancelled. Availability check becomes a simple range SELECT. The pre-aggregation trades write complexity for dramatically faster reads — essential at Booking.com scale where millions of availability checks are made per second.”
}
},
{
“@type”: “Question”,
“name”: “How do you prevent double-booking when multiple users book the last room simultaneously?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Use database-level concurrency control. Option 1 — Pessimistic locking: SELECT … FOR UPDATE on the availability record before checking and decrementing. Only one transaction proceeds; others wait. Option 2 — Optimistic locking: include a version field on the room_type_availability row. Read the current version, insert the reservation, then UPDATE availability SET available = available – 1, version = version + 1 WHERE version = :read_version. If 0 rows updated (someone else incremented the version), retry. Option 3 — Database constraint: a unique partial index preventing more confirmed reservations than total rooms, enforced at the DB level. The key principle: the availability check and the reservation insert must be atomic — either in the same transaction with appropriate isolation, or using an optimistic lock with retry.”
}
},
{
“@type”: “Question”,
“name”: “How do you implement dynamic pricing for hotel rooms?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Store price rules in a price_rules table: (hotel_id, room_type_id, start_date, end_date, price_per_night, priority). At reservation creation time: for each night in the stay, find the applicable rule (the highest-priority rule whose date range covers that night). Sum the nightly prices. Store the total on the reservation. Lock the price at booking time — later rule changes don’t affect existing reservations. Rules hierarchy: override rules (specific date ranges, highest priority) beat seasonal rules (seasonal adjustments) beat the base price. For real-time demand-based pricing: a pricing engine runs continuously, adjusting prices based on occupancy rate (if a room type is 80%+ booked, raise price by 20%). The pricing engine writes new price rules; the reservation flow reads them.”
}
},
{
“@type”: “Question”,
“name”: “How do you handle hotel search by location and filters efficiently?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Location search: store hotel coordinates (lat, lng) in a PostGIS geometry column. Index with GIST. Query: ST_DWithin(location, ST_MakePoint(:lng, :lat), :radius_meters). Filter on star_rating, price_range, amenities (GIN index on JSONB amenities column). Availability filter: the expensive part — for each matching hotel, check if any room type has availability for the requested dates. Pre-compute daily availability counts per (hotel, room_type, date) in a separate table for fast joins. Sort results by: relevance score (rating * review_count), price, distance. Cache popular searches (city + date range + filters) in Redis with 5-minute TTL. For Booking.com scale: Elasticsearch indexes all hotel metadata and availability, updated by a change data capture pipeline from the primary database.”
}
},
{
“@type”: “Question”,
“name”: “How does the check-in and room assignment process work?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “At booking time: a room type is reserved, not a specific room. This gives the hotel flexibility. At check-in: the front-desk system calls assignRoom(reservation_id). The system selects an available physical room of the correct type that is not under maintenance and has no other confirmed check-in for today. SELECT room_id FROM rooms WHERE room_type_id = :type AND status = AVAILABLE AND room_id NOT IN (active check-ins today) LIMIT 1 FOR UPDATE. This FOR UPDATE lock prevents two front-desk agents from assigning the same room concurrently. Update reservation.room_id = :assigned and status = CHECKED_IN. If no rooms are available (overbooking edge case): escalate to manager — offer an upgrade, a neighboring hotel voucher, or compensation. The system tracks overbooking rates per hotel for vendor management.”
}
}
]
}
Asked at: Airbnb Interview Guide
Asked at: Uber Interview Guide
Asked at: Lyft Interview Guide
Asked at: Shopify Interview Guide
See also: Scale AI Interview Guide 2026: Data Infrastructure, RLHF Pipelines, and ML Engineering