🚀 SQL Project Series #18

Hotel Booking Analytics 🏨

Analyze hotel bookings, guests, rooms, payments, and occupancy trends using SQL to improve revenue, customer satisfaction, and operational efficiency.

🎯 Business Objectives
✅ Track hotel bookings
✅ Monitor room occupancy
✅ Analyze guest demographics
✅ Measure booking cancellations
✅ Evaluate room performance
✅ Analyze revenue trends
✅ Optimize pricing strategy
✅ Build hotel management dashboards

📂 Step 1: Create Database
CREATE DATABASE hotel_booking_db;
USE hotel_booking_db;

📂 Step 2: Create Guests Table
CREATE TABLE guests (
guest_id INT PRIMARY KEY,
guest_name VARCHAR(100),
gender VARCHAR(10),
city VARCHAR(50),
check_in_date DATE,
check_out_date DATE
);

📂 Step 3: Create Rooms Table
CREATE TABLE rooms (
room_id INT PRIMARY KEY,
room_type VARCHAR(50),
room_price DECIMAL(10,2),
room_status VARCHAR(20)
);

📂 Step 4: Create Bookings Table
CREATE TABLE bookings (
booking_id INT PRIMARY KEY,
guest_id INT,
room_id INT,
booking_date DATE,
booking_status VARCHAR(20),
payment_amount DECIMAL(10,2),
FOREIGN KEY (guest_id) REFERENCES guests(guest_id),
FOREIGN KEY (room_id) REFERENCES rooms(room_id)
);

📂 Step 5: Insert Sample Guests
INSERT INTO guests VALUES
(1,'Rahul Sharma','Male','Mumbai','2025-01-10','2025-01-13'),
(2,'Priya Verma','Female','Delhi','2025-01-12','2025-01-15'),
(3,'Amit Patel','Male','Pune','2025-01-14','2025-01-16'),
(4,'Sneha Joshi','Female','Bangalore','2025-01-15','2025-01-18'),
(5,'Rohan Gupta','Male','Hyderabad','2025-01-18','2025-01-20');

📂 Step 6: Insert Sample Rooms
INSERT INTO rooms VALUES
(101,'Standard',3000,'Occupied'),
(102,'Deluxe',5000,'Available'),
(103,'Suite',8500,'Occupied'),
(104,'Standard',3000,'Available'),
(105,'Deluxe',5000,'Occupied');

📂 Step 7: Insert Sample Bookings
INSERT INTO bookings VALUES
(1001,1,101,'2025-01-05','Confirmed',9000),
(1002,2,102,'2025-01-06','Confirmed',15000),
(1003,3,103,'2025-01-07','Cancelled',0),
(1004,4,104,'2025-01-08','Confirmed',9000),
(1005,5,105,'2025-01-10','Confirmed',10000);

🧠 SQL Concepts You'll Practice
✔ DDL & DML
✔ Joins
✔ Aggregate Functions
✔ GROUP BY
✔ HAVING
✔ CASE WHEN
✔ Date Functions
✔ CTEs
✔ Window Functions
✔ Ranking Functions

📊 Business KPIs You Can Build
📈 Total Bookings
📈 Confirmed Bookings
📈 Cancelled Bookings
📈 Booking Cancellation Rate
📈 Total Revenue
📈 Average Booking Value
📈 Average Length of Stay
📈 Occupancy Rate
📈 Revenue by Room Type
📈 Revenue by Month
📈 Room Utilization
📈 Available vs Occupied Rooms
📈 Most Popular Room Type
📈 Guest Retention Rate
📈 Repeat Guests
📈 Peak Booking Days
📈 Seasonal Booking Trends
📈 City-wise Guest Distribution
📈 Customer Lifetime Value
📈 Executive Hotel Dashboard

🎯 This project reflects real-world SQL analysis performed by Hotel Revenue Analysts, Hospitality Analysts, Operations teams, and Business Intelligence professionals to optimize occupancy, pricing, and customer experience.

💡 Double Tap ❤️ For More