🚀 SQL Project Series #23

Movie Ticket Booking Analytics 🎬

Analyze movies, theatres, customers, bookings, and payments using SQL to improve occupancy, revenue, and customer experience.

🎯 Business Objectives
✅ Analyze ticket bookings
✅ Track movie performance
✅ Measure theatre occupancy
✅ Analyze customer behavior
✅ Monitor payment trends
✅ Identify peak show timings
✅ Optimize pricing strategy
✅ Build executive dashboards

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

📂 Step 2: Create Customers Table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
gender VARCHAR(10),
city VARCHAR(50),
signup_date DATE
);

📂 Step 3: Create Movies Table
CREATE TABLE movies (
movie_id INT PRIMARY KEY,
movie_name VARCHAR(100),
genre VARCHAR(50),
language VARCHAR(30),
duration_minutes INT
);

📂 Step 4: Create Theatres Table
CREATE TABLE theatres (
theatre_id INT PRIMARY KEY,
theatre_name VARCHAR(100),
city VARCHAR(50),
total_seats INT
);

📂 Step 5: Create Bookings Table
CREATE TABLE bookings (
booking_id INT PRIMARY KEY,
customer_id INT,
movie_id INT,
theatre_id INT,
show_date DATETIME,
seats_booked INT,
ticket_amount DECIMAL(10,2),
booking_status VARCHAR(20),
payment_method VARCHAR(30),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (movie_id) REFERENCES movies(movie_id),
FOREIGN KEY (theatre_id) REFERENCES theatres(theatre_id)
);

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

📂 Step 7: Insert Sample Movies
INSERT INTO movies VALUES
(101,'Leo','Action','Tamil',164),
(102,'Pushpa 2','Action','Telugu',180),
(103,'12th Fail','Drama','Hindi',147),
(104,'Inside Out 2','Animation','English',96);

📂 Step 8: Insert Sample Theatres
INSERT INTO theatres VALUES
(201,'PVR Phoenix','Mumbai',250),
(202,'INOX Select City','Delhi',220),
(203,'Cinepolis Seasons','Pune',180),
(204,'PVR Orion','Bangalore',240);

📂 Step 9: Insert Sample Bookings
INSERT INTO bookings VALUES
(1001,1,101,201,'2025-02-01 18:30:00',2,900,'Confirmed','UPI'),
(1002,2,102,202,'2025-02-01 20:00:00',3,1350,'Confirmed','Credit Card'),
(1003,3,103,203,'2025-02-02 16:00:00',1,350,'Cancelled','UPI'),
(1004,4,104,204,'2025-02-02 19:30:00',4,1800,'Confirmed','Debit Card'),
(1005,5,101,201,'2025-02-03 21:00:00',2,900,'Confirmed','Wallet');

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

📊 Business KPIs You Can Build
📈 Total Bookings
📈 Total Tickets Sold
📈 Total Revenue
📈 Average Ticket Price
📈 Average Booking Value
📈 Booking Cancellation Rate
📈 Occupancy Rate
📈 Revenue by Movie
📈 Revenue by Theatre
📈 Revenue by City
📈 Most Popular Movie
📈 Most Popular Genre
📈 Peak Booking Hours
📈 Peak Show Timings
📈 Weekend vs Weekday Bookings
📈 Payment Method Distribution
📈 Customer Retention Rate
📈 Repeat Customers
📈 Top Spending Customers
📈 Theatre Utilization
📈 Language-wise Revenue
📈 Monthly Revenue Trend
📈 Movie Performance Dashboard
📈 Executive Booking Dashboard

🎯 This project reflects real-world SQL analysis performed by cinema chains, online ticketing platforms like BookMyShow, entertainment companies, and Business Intelligence teams to optimize occupancy, improve customer experience, and maximize revenue.

Double Tap ❤️ For More