Lilly Hooper
Rockbuster SQL
Project
3
Rockbuster Stealth LLC is a movie rental company that previously had stores around the world. They are facing stiff competition from streaming services like Netflix and have launched a Stealth Management team to use existing licenses to launch an online video rental service.
​
I have been hired by Rockbuster Stealth LLC to analyze existing customer and movie rental data. In this project, I helped the team upload data into a relational database management system. I answered ad hoc questions and completed an analysis presentation. This information was essential for Rockbuster to remain competitive in the future. The answers and suggestions I produced will help Rockbuster remain competitive in the future and launch their online rental service.
Data:
The data I was given contained inventory, customers, payment information, etc. I analyzed and queried the data in PostgreSQL.
Skills/Tools
-
SQL
-
Database querying, filtering, cleaning
-
Joining Tables
-
Subqueries and CTEs
-
Relational Databases
-
Data Dictionaries
Questions Asked:
Which movies contributed most/least to revenue gain?
What was average rental duration for videos?
Which countries are Rockbuster customers based in?
Where are customers with high lifetime value?
Do sales figures vary between geographic regions?
Movie Rental Analysis: Overview
Project Steps
1
The first step of my project required a full understanding of the data. I extracted an ERD of my data (figure 1) and described each table (figure 2). This would later be seen in my data dictionary.
Figure 1
Figure 2
3
After I completed the initial queries with descriptive statistics, I ran some queries to return lists of filtered data. The Rockbuster team asked for several lists. Some of this filtered data included films that were longer than 120 minutes but had a rental rate more than 2.99, films with a replacement cost less than 14.99, and films with a rating of either G or PG. The inventory team asked for a count of movies, the average rental rate or all movies, and the max and minimum rental durations.
​
Many details and findings resulted from this step. The results will later be added to the final presentation to stakeholders. If you are interested in viewing more of my queries, please click this link to my GitHub.
5
In the last step of this project, I performed subqueries and CTEs or common table expressions on my data. This essentially allowed me to find out how many of the top 5 customers are based within what Country. Using these remaining results, I put together a final cleaned data dictionary (link here) and presentation (link here) to the Rockbuster team.
2
I started to perform several queries, attempting to begin answering the ad hoc questions posed by the company. I selected various tables and found descriptive statistics for the pertinent tables. A sample of my code can be seen below in figure 3. Throughout this step, I compared cost of queries to have a better understanding of using company resources wisely.
Figure 3
4
In this step I cleaned up the data a bit, meaning I checked the data for duplicates, non-uniform and missing values. After this, I honed in on my join skills and joined data to produce valuable answers to the stakeholder questions. Through these queries, I found the top customers, cities, and countries for Rockbuster. This important info would be presented in the final presentation. See below an example query and result from this step. Figure 4 is the top countries by customer count.
Figure 4
Final Results
My findings are packaged within the final presentation link. Listed below are what comprises the presentation:
-
customer base information (countries and cities)
-
Top customers and stores
-
Top Movies
-
Key Metrics Surrounding Rentals
I found that the top country in regard to customer base for Rockbuster is India. The top city is Aurora in the U.S. The customer Sara Perry has the highest total payment ammount. Academy Dinosaur is the most rented film. The lowest cost to replace a movie was 10 dollars and the max rental duration was 7 days.
Recommendations
I recommended that Rockbuster check their data. The current data I worked with throughout the project had limited rows. it may be beneficial for Rockbuster to search for other data sources. I also recommended that Rockbuster understand how a new streaming service would impact their existing and top customers.
Challenges
I faced several challenges throughout this project. Prior to the work, I had limited SQL knowledge. I had never loaded data into PostgreSQL before. At first, it was a challenge to get used to the system, but thanks to google, I was able to find all the solutions I needed.
​
In the future, I plan to continue to practice my SQL skills. As with anything, it takes practice to be good. I plan to continue to review the advanced SQL topics that confused me at first sight. It would be beneficial to review these new learned topics with various data sources.