Rockbuster Stealth LLC

PROBLEM:

• Streaming services are a fierce competitor to the Rockbuster Stealth. To remain competitive, they made the decision to launch an online video rental service, but they need specific information on the customers, movies, and markets they should focus on.

OBJECTIVE:

• Perform an analysis to help with the launch strategy for the new online video service and to answer management’s business questions.

KEYNOTES

• To find the answers to the key questions, I used joins, subqueries, and common table expressions.

• Rockbuster in his database has 599 customers in 108 different countries, 1000 films in 6 different languages, 20 different genres, and 200 actors.

• On average films are rented for a period of 5 days.

SKILLS

Relational databases

Joining tables

Database querying

Subqueries

Filtering, Cleaning & Summarizing

Common table expressions

TOOLS

PostgreSQL

PowerPoint

Tableau

Rockbuster

DATA

Rockbuster Stealth LLC
Key Insights

Which movies/categories contributed the most to revenue gain?

• “Sport" is the most profitable Category.

• "Telegraph Voyage" is the most profitable movie.

WITH profitable_films_cte AS
(SELECT A.title,
SUM(D.amount) AS total_amount
FROM film A
INNER JOIN inventory B ON A.film_id = B.film_id
INNER JOIN rental C ON B.inventory_id = C.inventory_id
INNER JOIN payment D ON C.rental_id = D.rental_id

GROUP BY 1
ORDER BY total_amount DESC
LIMIT 10),
film_category_name_cte AS
(SELECT A.title,
C.name
FROM film A
INNER JOIN film_category B ON A.film_id = B.film_id
INNER JOIN category C ON B.category_id = C.category_id
GROUP BY 1,2)
SELECT profitable_films_cte.title,
film_category_name_cte.name AS category,
total_amount
FROM profitable_films_cte, film_category_name_cte;

Do sales figures vary between geographic regions?

• Sales are higher in areas with a higher concentration of customers.

SELECT country,
COUNT(A.customer_id) AS customer_count,
SUM(amount) AS total_payment
FROM customer A
INNER JOIN address B ON A.address_id = B.address_id
INNER JOIN city C ON B.city_id = C.city_id
INNER JOIN country D ON C.country_ID = D.country_ID
INNER JOIN payment E ON a.customer_id = E.customer_id
GROUP BY country;

Where are customers with a high lifetime value based?

• Eleanor Hunt is a customer with the highest lifetime value ($211.55).

SELECT
A.first_name,
A.last_name,
D.country,
C.city,
SUM(E.amount) AS total_amount
FROM payment E
INNER JOIN customer A ON E.customer_id=A.customer_id
INNER JOIN address B ON A.address_id = B.address_id
INNER JOIN city C ON B.city_id = C.city_id
INNER JOIN country D ON C.country_id = D.country_id
GROUP BY A.customer_id, D.country, C.city
ORDER BY total_amount DESC
LIMIT 10;

RECOMMENDATIONS:

Concentrate more on the markets of India, China, and the United States because they have by far the most existing clients and revenue.

The categories of "Sport," "Foreign," "Family," "Documentary," and "Animation" should be expanded because they are the most profitable.

Customers who have a high lifetime value should be rewarded for their loyalty.

DELIVERABLES:

Rockbuster Stealth LLC

SKILLS

TOOLS

DATA

Rockbuster Stealth LLC Key Insights

Rockbuster Stealth LLC
Key Insights