Rockbuster:
Movie Rental Data

Act as a Data Analyst hired through the Business Intelligence (BI) department for Rockbuster Stealth. Load fictional movie data into a relational database management system. Use Structured Query Language (SQL) to analyze data, practice skills and answer increasingly complex business questions. Aid in supporting a new launch strategy for online video services in order to compete with Netflix and Amazon Prime.

Photo Credit

Project Overview ------------------------------------------------------------------------------------------------------------------------------------------------------------

Data

Rockbuster data was provided by CareerFoundry Data Analytics Course in a zip file.

Data contains film inventory, customer, and payments and other items.

Objective

Answer a series of business questions providing data-driven answers to use in a new company strategy:

Revenue gain
Average rental length
Customer base by location and lifetime value
Sales figures by geographic region

Tools

PostrgreSQL
DbVisualizer (RDBMS/Schema)
Excel
Tableau

Skills

Data cleaning
Exploratory Data
Descriptive Statistics
Online Analytical Processing DataBases (OLAP) vs Relational Database Management Systems (RDBMS)
Setting up database environment using PostgresQL
Data storage structure
Development, Staging, and Production (DSP) Models
Tableau Visualizations with SQL CSV files

SQL syntax:

Common, CRUD, Joins, Where, Group By, Having, Order By, Limit, Case, Subqueries, Common Table Expressions

-------------------------------------------------------------------------- Process -------------

Introduction

Understand databases:

(structured, unstructured, semi-structured)

Set up data environment in PostgreSQL

Relational Databases

Data storage structure

Data Types

Data Dictionary

Entity Relationship Diagram (ERD)

Schema

PostgresSQL

Exploratory Data Analysis

Querying questions from simple to complex:

Common commands

CRUD

Syntax Order

Cleaning Dirty/Duplicate Data

Joins

Subqueries

Common Table Expressions

Data Visualization

Saving SQL results as CSV files

Expressing the answers as visuals in Tableau

Presentation

Prepare an Excel file with SQL query and output

Finalize Data Dictionary

Tableau Visuals

Photo Credit

--------------------------------------------- Analysis -----------------------------------------

Business Questions

1. Statistics available in the film table.

2. Top 10 countries Rockbuster’s customers are based.

3. Identifying the top 10 cities within the top 10 countries.

4. Identifying the top 5 customers from the top 10 cities who’ve spent the most at Rockbuster .

Rental Prices

1. Statistics available in the film table.

Average rental prices from minimum to maximum cost was reviewed.

SQL Query:

SELECT MIN(rental_rate) AS Min_Rent,

MIN(replacement_cost) AS Min_Cost,

MAX(rental_rate) AS Max_Rent,

MAX(replacement_cost) AS Max_Cost,

AVG(rental_rate) AS Avg_Rent,

AVG(replacement_cost) AS Avg_Cost,

COUNT(rental_rate)AS Count_Rent_Values,

COUNT(replacement_cost) AS Count_Replacement_Cost,

COUNT(*) AS Count_rows

FROM film;

Top Countries, Cities, Customers

2. Top 10 countries Rockbuster’s customers are based.

3. Identifying the top 10 cities within the top 10 countries.

4. Identifying the top 5 customers from the top 10 cities who’ve spent the most at Rockbuster .

Photo Credit

Top Countries

In SQL the identification of the top Countries was found to be:

China - repeated x3

Switzerland

Peru

Greece

United States

Hungary

Brazil

Bahrain

Top Cities

In SQL the identification of the top Cities was found to be:

Tieli

Lausanne

Sullana

Patras

Jinzhou

Jining

Garden Grove

Szkesfehrvr

Juiz De Fora

al-Manama

Top Customers

In SQL the identification of the top Customer that spent the most was found to be:

Terrence J., China

Freddie D., Peru

Wade D., Switzerland

Austin C., Chine

Forsythe P., Greece

**If this was not fictional data, these names would not be available to the public.**

Final Report -----------------------------------------------------------------------------

Project Reflections

To answer the business questions proposed by Rockbuster's BI team, understanding how to read RDBMS/Schema, and code in Structured Query Language was paramount.
In order to answer the more complex business questions it was necessary to learn to use:
- Aggregate Functions
- Joins
- Subqueries
- Common Table Expression

Aggregate Function
- These functions were used to determine price point range of movie rentals
- This provided insight on minimum, average, and maximum costs as well as counting average cost.
Joins
- Joins came in useful when looking for additional information regarding the top counties, cities and customers.
- Joins allows for the back tracking into other tables to provided the answers
Subqueries/Common Table Expressions
- These are more complex SQL codes that required the previous join codes to reach an even more detailed answer.
- Subqueries are more expensive when compared to Common Table Expressions and therefore, in this project, CTEs were used over Subqueries.

Take Away

The top 10 countries, cities and customers were addressed.
This provides insight for Rockbuster to move forward in launching their online video rental strategy in these area's first.
Further data derived from the launch can strengthen the proposed course of advertisements and promotions.

Deliverables

Rockbuster: Movie Rental Data

Project Overview ------------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------- Process -------------

--------------------------------------------- Analysis -----------------------------------------

Final Report -----------------------------------------------------------------------------

Rockbuster:
Movie Rental Data