📘 SQL-Based Business Analytics

An in-depth analysis of a DVD rental database to drive business strategy using SQL.

Project Overview

This project involved a 14-day intensive SQL practice focused on the Sakila DVD rental database. The primary goal was to move beyond basic queries and leverage advanced SQL techniques to extract actionable business intelligence. The analysis covers customer behavior, film performance, inventory management, and revenue trends, demonstrating a strong capability to answer complex business questions using only database queries.

Project Artifacts

View Code on GitHub

Dataset Overview

Source: Sakila Sample Database (PostgreSQL Version)
Environment: The analysis was performed in a local PostgreSQL environment.
Description: The Sakila database is a widely-used sample dataset that models a fictional DVD rental store. It features a rich schema with tables for films, actors, customers, rentals, and payments, making it an ideal environment for practicing complex SQL queries and business analysis.

Methodology & SQL Highlights

The analysis was conducted entirely within a SQL environment, focusing on a structured, daily progression of complexity. Key SQL concepts and techniques employed include:

Complex Joins: Multi-table joins (INNER, LEFT, RIGHT) across the entire schema to connect customer, rental, payment, and film data.
Aggregation & Grouping: Extensive use of COUNT(), SUM(), AVG() with GROUP BY and HAVING clauses for summarization.
Subqueries & CTEs: Used Common Table Expressions (CTEs) and subqueries (including EXISTS/NOT EXISTS) to break down complex logic and improve query readability.
Window Functions: Applied advanced window functions like RANK(), DENSE_RANK(), LAG(), SUM() OVER(), and AVG() OVER() for ranking, time-series analysis, and calculating moving averages.
Date/Time Functions: Utilized functions like DATE_TRUNC, EXTRACT, and date arithmetic to analyze trends over time.

Key Business Questions & Insights

The project answered several critical business questions, providing data-driven insights for strategic decision-making. Below is a summary of key findings.

Business Question	SQL Technique Used	Key Insight / Outcome
Which film categories generate the most revenue?	`JOIN`, `SUM()`, `GROUP BY`, `ORDER BY`	Identified the top 3 most profitable film categories, allowing for targeted marketing and inventory investment.
Who are our most valuable customers?	`DENSE_RANK()`, `SUM()`, Window Functions	Ranked customers by total payment amount, revealing that a small percentage of customers contribute a majority of the revenue.
Are there films in stock that are never rented?	`LEFT JOIN` with `IS NULL` and `GROUP BY`	No films in inventory were found without rental history, indicating strong rental circulation and efficient stock usage.
How is rental revenue trending month-over-month?	`DATE_TRUNC`, `SUM() OVER()`	Calculated cumulative monthly revenue, showing a positive growth trend and identifying peak rental months.
Which staff members are the most productive?	`JOIN`, `COUNT()`, `GROUP BY`	Identified top-performing staff by the number of transactions processed, useful for performance reviews and incentives.
Which films are rented more than the average?	Subquery / CTE, `AVG()`, `GROUP BY`, `HAVING`	Pinpointed popular films that outperform the average, providing a list of titles to promote.

Analysis in Action: Sample Outputs

Below are a few examples of the query outputs that directly answered the business questions and informed the final recommendations.

Screenshot of SQL query output showing top 3 most profitable film categories. — **Fig 1:** Top 3 most profitable film categories by total revenue.

Screenshot of SQL query output showing top customers ranked by payment amount. — **Fig 2:** Top customers ranked by total payment amount using DENSE_RANK().

Screenshot of SQL query output showing staff performance by transaction count. — **Fig 3:** Staff members ranked by the total number of transactions processed.

Screenshot of SQL query output listing films in inventory but never rented. — **Fig 4:** A list of films available in inventory but never rented.

Conclusion & Recommendations

The SQL analysis of the Sakila database successfully uncovered significant patterns in sales, customer behavior, and operations. The findings directly translate into actionable business strategies:

Marketing: Focus promotional efforts on the top-performing film categories and specific popular titles to maximize revenue.
Customer Relations: Develop a loyalty program for the top-ranked customers to enhance retention and encourage continued spending.
Operations: Recognize and reward top-performing staff members to boost morale and maintain high productivity.

This project demonstrates the power of SQL not just as a data retrieval tool, but as a robust analytical engine capable of providing deep, strategic insights directly from a relational database.