Rockbuster Video Rental
SQL Customer Analysis
Background
Rockbuster Stealth LLC is a traditional brick-and-mortar video rental company. Facing competition from streaming services like Netflix, Rockbuster is looking to break into the streaming market.
Objective
Write moderately complex SQL queries to help with the launch strategy for the new online video service.
Create a data dictionary for the database.
Deliver a presentation of findings answering questions from business intelligence team.
Context
This project was created as a part of the Career Foundry data analytics course. It utilizes PostgreSQL as the primary analysis tool, DbVisualizer for ERD representation, and MS Excel.
The presentation to stakeholders was created in Tableau. Visualizations can be found here.
Steps
Examine the database and create an Entity Relationship Diagram (ERD) identifying links between databases.
Perform initial filtering of the data and identify keys.
Summarize, clean, and join the data.
Perform subqueries and utilize Common Table Expressions (CTEs) to answer complex key questions.
Present results to BI team.
Step 1
Using DBVisualizer, I created an ERD that displayed all the connections within the Rockbuster database.
This was vital for understanding joins and performing subqueries throughout the rest of the analysis.
Step 2
Using the ERD, I identified the primary keys throughout the database. I also created new tables as needed to connect information in ways that the Rockbuster database had not previously utilized.
Step 3
With the data understood and connected, I began the analysis. This involved identifying KPIs and grouping datapoints for deeper analysis
Step 4
The second round of analysis yielded the final results. Top demographics, geographies, and categories of movies were identified using CTEs to conveniently manage large query chains.
Step 5
With the necessary information gleaned and gathered, I created visualizations of the results in Tableau and a deliverable to the business intelligence team in Powerpoint.
