Hotel Reservation Analysis SQL

Unlocking Insights from Hotel Reservation Data: A Comprehensive Analysis

Introduction
The hotel industry relies heavily on data to make informed decisions and enhance guest experiences. This report delves into the analysis of a hotel reservation dataset, aiming to uncover patterns and trends that can drive operational efficiency and boost guest satisfaction. Through SQL queries, we extracted valuable insights regarding booking trends, guest preferences, and key operational metrics.

Dataset Overview
The dataset used in this analysis consists of 700 rows and 12 columns, capturing various aspects of hotel reservations, including:

• Booking_ID: Unique identifier for each reservation.
• no_of_adults: Number of adults in the reservation.
• no_of_children: Number of children in the reservation.
• no_of_weekend_nights: Number of weekend nights in the reservation.
• no_of_week_nights: Number of weekday nights in the reservation.
• type_of_meal_plan: Meal plan chosen by guests.
• room_type_reserved: Type of room reserved.
• lead_time: Days between booking and arrival.
• arrival_date: Date of arrival.
• market_segment_type: Market segment to which the reservation belongs.
• avg_price_per_room: Average price per room in the reservation.
• booking_status: Status of the booking (e.g., confirmed, cancelled).

Data Cleaning for Hotel Reservation Dataset

• Handling Missing values

SELECT * 
FROM reservations
WHERE no_of_adults IS NULL
   OR no_of_children IS NULL
   OR no_of_weekend_nights IS NULL
   OR no_of_week_nights IS NULL
   OR type_of_meal_plan IS NULL
   OR room_type_reserved IS NULL
   OR lead_time IS NULL
   OR arrival_date IS NULL
   OR market_segment_type IS NULL
   OR avg_price_per_room IS NULL
   OR booking_status IS NULL;

• Removing Duplicates

select Booking_ID, count(*) from reservation Res
group by Booking_ID
having count(*) >1;

• Standardizing Data Formats

update reservation 
set arrival_date = str_to_date(arrival_date, '%d-%m-%Y');

• Added Column Booking_Date

alter table reservation
add column Booking_Date date;
update reservation
set Booking_Date = date_sub(arrival_date,interval lead_time Day);

Analysis and Insights

1. What is the total number of reservations in the dataset?
   Insight: There are 700 reservations in the dataset.

select count(*) as Total_Reservations from reservation;

2. Which meal plan is the most popular among guests?
Insight: Meal Plan 1 is the most popular choice among guests.

select type_of_meal_plan, 
count(type_of_meal_plan) as count from reservation
Where booking_status = "Not_Canceled"
group by type_of_meal_plan
order by count desc
limit 1;

3. What is the average price per room for reservations involving children?
Insight: The average price per room for reservations involving children is approximately $145.

select avg(avg_price_per_room) as 
overall_average_price__per_room_for_children from reservation
where no_of_children > 0;

4. How many reservations were made for the year 20XX (replace XX with the desired year)?
Insight: In 2018, there were 577 reservations.

select year(Booking_Date) as Year, 
count(year(Booking_Date)) as Reservations_in_year from reservation
where year(Booking_Date) = "2018"
group by year(Booking_Date);

5. What is the most commonly booked room type?
Insight: Room Type 1 is the preferred choice with 534 bookings.

select room_type_reserved,count(room_type_reserved) count from reservation 
group by room_type_reserved
order by count desc
limit 1;

6. How many reservations fall on a weekend (no_of_weekend_nights > 0)?
Insight: 383 reservations include weekend nights.

select count(*) as weekend_reservations
from reservation
where no_of_weekend_nights > 0;

7. What is the highest and lowest lead time for reservations?
Insight: 443 is the highest lead time and 0 is the lowest lead time

select max(lead_time) as Highest_lead_time,
 min(lead_time) as Lowest_lead_time
from reservation;

8. What is the most common market segment type for reservations?
Insight: The online market segment dominates with 518 reservations.

select market_segment_type, count(market_segment_type) as count 
from reservation
group by market_segment_type
order by count desc
limit 1;

9. How many reservations have a booking status of “Confirmed”?
Insight: Out of 700 reservations, 493 are confirmed, indicating a 70.43% success rate.

select count(*) as confirmed_reservation from reservation
where booking_status = "Not_Canceled";

10. What is the total number of adults and children across all reservations?
Insight: The total number of adults and children is 1385. Where Adults and children are 1316 and 69 respectively.

select sum(no_of_Adults) as Total_Adults,
 sum(no_of_children) as Total_children,
 sum(no_of_adults + no_of_children) as Total_Number_of_Adults_and_Children 
 from reservation;

11. What is the average number of weekend nights for reservations involving children?
Insight: Reservations with children suggest a preference for an average of one weekend night stay.

select avg(no_of_weekend_nights) as Averge_Weekend_Nights from reservation
where no_of_children > 0;

12. How many reservations were made in each month of the year?
Insight: October records the highest number of reservations, while January records the lowest.

select month(Booking_Date) as month_Number, 
monthname(Booking_Date) as month,
count(*) as count_of_reservation from reservation
group by month(Booking_Date),monthname(Booking_Date)
order by month_Number;

13. What is the average number of nights (both weekend and weekday) spent by guests for each room type?
Insight: Room Type 4 guests prefer longer stays (average of 3.45 nights), while Room Type 5 guests opt for shorter durations.

select room_type_reserved,
 avg(no_of_week_nights+no_of_weekend_nights) as avg_nights from reservation
where booking_status = "Not_Canceled"
group by room_type_reserved
order by avg_nights desc;

14. For reservations involving children, what is the most common room type, and what is the average price for that room type?
Insight: For reservations involving children, Room Type 1 is preferred, with an average room price of $123.12.

select room_type_reserved,count(*) as room_type, 
avg(avg_price_per_room) as average_price from reservation
where no_of_children > 0
group by room_type_reserved
order by room_type desc
limit 1;

15. Find the market segment type that generates the highest average price per room.
Insight: Online bookings generated the highest average price per room, reaching approximately $112.46.

select market_segment_type,
avg(avg_price_per_room) as average_price from reservation
group by market_segment_type
order by average_price desc
limit 1;

Conclusion

This analysis provides a comprehensive overview of booking trends, guest preferences, and operational insights within the hotel industry. By leveraging SQL to query and analyze the dataset, we can make data-driven decisions to optimize room allocations, personalize guest experiences, and enhance revenue management strategies. These insights are crucial for staying competitive and ensuring guest satisfaction in the dynamic hospitality landscape.

For the source code, check GitHub

Thank you for reading.

You can connect with me on LinkedIn

You can also reach me via my email mkolapo165@gmail.com.