This case study presents a PostgreSQL data analysis project built around the Rockbuster relational database.
The project focuses on using SQL to explore customer distribution, revenue patterns, market performance, top customers, data quality, and business insights across multiple related database tables.
The goal was to turn relational database outputs into clear, business-focused insights that could support management decision-making.
This project demonstrates how SQL can be used not only to query data, but also to answer real business questions around revenue, customers, geography, and market opportunity.
—
Project Overview
Rockbuster Stealth LLC is a fictional movie rental company looking to use its existing customer and rental data to support strategic decisions.
The company has a relational database containing information about customers, rentals, payments, films, categories, inventory, countries, cities, addresses, and staff activity.
The purpose of this project was to analyse that database using PostgreSQL and identify insights that could help the business understand where its strongest markets are, who its most valuable customers are, and how revenue is distributed across different locations.
The final outputs were prepared for both technical and non-technical audiences, including SQL scripts, query outputs, a data dictionary, and a business-facing presentation.
—
Business Problem
A company with a large relational database can collect a lot of information, but raw tables alone do not create business value.
Management needs clear answers to questions such as where customers are located, which markets are strongest, who the highest-value customers are, and which areas may deserve more business attention.
The key business problem was:
How can Rockbuster use SQL analysis to understand customer distribution, revenue contribution, and market performance across its relational database?
This project was designed to answer that question through structured SQL querying, data profiling, aggregation, joins, subqueries, and stakeholder-ready reporting.
—
Project Goals
The main goals of this project were to:
Identify which countries had the highest customer counts.
Analyse cities within priority countries to understand customer concentration.
Identify top customers by total amount paid.
Explore revenue patterns across customers and geographic markets.
Use SQL joins to connect customer, payment, rental, address, city, and country data.
Apply subqueries and common table expressions to answer more advanced business questions.
Review data quality and understand the structure of the database.
Prepare query outputs and findings for business stakeholders.
Communicate the analysis through clear deliverables, including a final presentation and supporting technical files.
—
Data Source
The project used the Rockbuster relational database.
The database included multiple connected tables covering areas such as:
Customers.
Payments.
Rentals.
Films.
Film categories.
Inventory.
Addresses.
Cities.
Countries.
Staff.
Stores.
This structure made the project useful for practising real SQL analysis because important business questions required joining multiple tables together.
—
Tools Used
This project was completed using:
PostgreSQL for relational database querying.
pgAdmin 4 for database management and SQL execution.
SQL for analysis, filtering, aggregation, joins, subqueries, and CTEs.
Excel for reviewing and storing query outputs.
Tableau Public for visualising selected outputs.
PowerPoint for the final business-facing presentation.
ERD documentation for understanding database relationships.
A data dictionary for documenting tables, fields, keys, and relationships.
—
SQL Techniques Used
This project included SQL techniques across different levels of complexity.
The analysis used SELECT, FROM, WHERE, ORDER BY, LIMIT, GROUP BY, HAVING, and DISTINCT to explore and summarise the data.
Aggregate functions such as COUNT, MIN, MAX, AVG, and SUM were used to calculate customer counts, payment totals, descriptive metrics, and revenue-related outputs.
CASE statements were used to create derived logic where needed.
Multi-table joins were used to connect customer, payment, rental, address, city, and country information.
The project also included INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, subqueries, common table expressions, and EXPLAIN for query planning and cost comparison.
These techniques were used in a business context rather than as isolated SQL exercises.
—
Analysis Approach
The analysis started with understanding the database structure.
Before answering business questions, I reviewed the entity relationship diagram, explored the available tables, checked important fields, and used the data dictionary to understand how the tables connected.
After reviewing the structure, I began writing SQL queries to explore customer geography, payment totals, rental activity, and market-level performance.
The analysis moved from basic querying and aggregation into more advanced logic using joins, subqueries, and common table expressions.
Query outputs were then exported and prepared for review so the results could be communicated clearly to both technical and business audiences.
—
Customer Geography Analysis
One important part of the analysis focused on customer geography.
The goal was to understand where Rockbuster customers were located and which countries or cities had the strongest customer concentration.
This required connecting customer records with address, city, and country tables.
By aggregating customer counts by country and city, the analysis helped identify priority markets where Rockbuster had a stronger customer base.
This type of analysis could support business decisions around regional marketing, localisation, customer support, and future expansion priorities.
—
Revenue Analysis
Another major part of the project focused on revenue contribution.
Payment data was joined with customer and location data to identify which customers and markets contributed the most revenue.
This helped answer questions such as which customers generated the highest total payment amounts and which geographic markets showed stronger commercial potential.
Revenue analysis is important because customer count alone does not always show business value. A smaller market with higher customer spending may be more valuable than a larger market with lower revenue per customer.
—
Customer Value Analysis
The project also explored top customers by total amount paid.
This required joining payment and customer data, aggregating total payment amounts, and ranking customers by revenue contribution.
This type of analysis can help a business identify high-value customers and understand where loyalty, retention, or targeted communication efforts may be most useful.
For a real company, this could support customer segmentation, VIP campaigns, retention strategies, or targeted promotional offers.
—
Data Quality and Profiling
The project also included data quality and profiling work.
Before relying on query outputs, it was important to understand the structure and reliability of the data.
This included checking table relationships, reviewing key fields, identifying duplicates or inconsistencies, and documenting the database structure through a data dictionary.
This part of the project helped ensure the analysis was grounded in a clear understanding of the database rather than simply running queries without context.
—
Key Findings
The analysis showed that customer and revenue distribution were not evenly spread across all markets.
Some countries and cities had stronger customer concentration, making them more important from a business planning perspective.
The revenue analysis showed that high-value customers could be identified by joining customer and payment data.
The project also demonstrated that SQL aggregation and multi-table joins can reveal useful business patterns that are not visible from individual tables alone.
By connecting customer, payment, address, city, and country data, the analysis created a clearer view of Rockbuster’s customer base and revenue opportunities.
—
Business Value
This project demonstrates how SQL can be used as a practical business intelligence tool.
A company could use this type of analysis to identify priority markets, understand customer value, monitor revenue distribution, support regional strategy, and prepare manager-friendly reporting from relational database outputs.
The main business value comes from turning technical database records into clear answers that support decision-making.
Instead of looking at disconnected tables, management receives structured insights about customers, locations, revenue, and market performance.
—
Final Deliverables
The final project deliverables included a manager-facing presentation of key findings, an Excel workbook containing SQL queries and outputs, a finalized data dictionary, SQL scripts, and supporting documentation.
These deliverables were designed to show both the technical SQL process and the final business interpretation.
The project was structured so that a technical reviewer could inspect the SQL logic, while a business stakeholder could understand the findings and recommendations without needing to read every query.
—
Recommendations
Based on the analysis, Rockbuster could use customer and revenue data to prioritise markets with stronger customer concentration and higher revenue contribution.
High-value customers could be segmented for retention campaigns or loyalty-based promotions.
Countries and cities with stronger customer counts could be considered for targeted marketing activity.
Markets with lower customer presence but meaningful revenue potential could be reviewed further to understand whether growth opportunities exist.
If this project were extended, I would add marketing spend, customer acquisition cost, customer lifetime value, streaming engagement data, and churn indicators to create a more complete business strategy model.
—
GitHub Project Files
The original project repository contains the technical SQL project files, including scripts, deliverables, query outputs, and documentation.
You can view the Rockbuster PostgreSQL data analysis project on GitHub.
I also created a separate Rockbuster SQL customer revenue analysis GitHub case study repository to support the public case study and portfolio backlink structure.
—
About This Project
This project was created as part of my data analytics portfolio to demonstrate practical SQL, relational database analysis, business reporting, and stakeholder communication skills.
It shows how SQL can be used to move from raw relational tables to business insight, helping decision-makers understand customer behaviour, revenue performance, and geographic opportunity.
—
About Me
I’m Elia Lanzuise, a Melbourne-based data analyst focused on transforming raw data into clear, actionable insights using SQL, Python, Tableau, Power BI, Excel, and dashboard development.
My work focuses on business performance analysis, customer behaviour, revenue analysis, operational insights, data visualisation, and turning complex datasets into practical decisions.
You can explore more of my work on my data analyst portfolio.
If your business needs help turning raw data into dashboards, reports, and clear insights, you can learn more about my business dashboard services.