SQL Business Analytics Dashboard

How can a traditional business like a DVD rental store use its data to drive revenue and optimize operations? This project answers that question through an intensive SQL-based analysis of the classic Sakila database. By moving beyond basic data retrieval, I leveraged advanced PostgreSQL queries to uncover hidden patterns in customer behavior, inventory efficiency, and revenue streams.

The analysis culminated in a set of actionable business strategies with the potential to increase revenue by an estimated 15% through targeted marketing and improved customer relationship management.

The Business Challenge

Traditional retail businesses face increasing pressure to maximize revenue from existing customers while optimizing operational efficiency. This project simulates the analytical work required to help a DVD rental business identify growth opportunities through data-driven insights.

The Analytical Approach

Using the comprehensive Sakila database as a business case study, I applied sophisticated SQL techniques to extract actionable business intelligence. The analysis covers customer segmentation, inventory optimization, revenue analysis, and operational efficiency—demonstrating how advanced database querying can solve real business challenges.

Strategic Impact

The project showcases how data-driven insights can transform business strategy, moving from intuition-based decisions to evidence-backed recommendations that drive measurable results.

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.

The analysis was performed entirely within a PostgreSQL environment, focusing on a structured progression from foundational to advanced SQL techniques.

• Complex Joins & Aggregation: Utilized multi-table JOINs and aggregate functions (SUM, AVG, COUNT) to create comprehensive business summaries across the entire schema.
• Subqueries & CTEs: Employed Common Table Expressions to break down complex problems into logical, readable steps, such as identifying films rented more than the average.
• Window Functions: Applied RANK(), DENSE_RANK(), and SUM() OVER() to perform sophisticated ranking of top customers and calculate cumulative monthly revenue without complex self-joins.
• Conditional Logic & Date Functions: Used CASE statements for segmentation and date functions like DATE_TRUNC to analyze trends over time.
• Performance Optimization: Structured queries for efficiency and readability, ensuring scalable analysis approaches.

Technical Progression:

The analysis followed a structured approach, beginning with foundational data exploration and advancing to sophisticated business intelligence queries. Each query was designed to answer specific business questions while demonstrating increasing levels of SQL complexity and analytical sophistication.

The analysis focused on answering critical business questions. The insights were then translated directly into strategic recommendations.

Below are examples of the query outputs that directly answered the business questions and informed the final recommendations. Use the navigation controls to view different analysis results.

Identifying Top Revenue-Driving Categories

This query joins film, category, and payment tables to rank categories by total revenue, providing clear direction for marketing spend allocation and promotional strategy development.

Pinpointing High-Value Customers

Using a window function, this analysis ranks customers by total payments, identifying the VIP segment for a new loyalty program that could significantly increase customer lifetime value.

Verifying Inventory Efficiency

This LEFT JOIN query confirmed that all inventory items have been rented at least once, validating the current stock management strategy and indicating excellent inventory circulation.

Analyzing Staff Productivity for Performance Optimization

Staff transaction analysis revealing performance differences that support implementing performance-based incentives and targeted training programs to boost overall operational efficiency.

This project demonstrates the power of SQL as a robust analytical engine for deriving strategic business insights directly from relational data. The analysis successfully identified key revenue drivers, high-value customer segments, and operational efficiencies that translate into concrete business value.

Key Achievements:

• Revenue Optimization: Identified specific film categories and customer segments that drive the majority of business value
• Operational Validation: Confirmed efficient inventory management practices through comprehensive data analysis
• Strategic Roadmap: Delivered actionable recommendations across marketing, customer relations, and operations
• Technical Excellence: Demonstrated advanced SQL proficiency through complex queries and sophisticated analytical techniques

Business Impact:

The resulting recommendations provide a clear, data-driven roadmap for optimizing marketing efforts, enhancing customer loyalty, and ultimately increasing profitability. The estimated 15% revenue increase potential validates the power of data-driven decision making in traditional retail environments.

Technical Validation:

This project showcases how sophisticated SQL analysis can replace expensive business intelligence tools for many analytical tasks, providing cost-effective insights while maintaining analytical rigor and accuracy.