ANSWERING BUSINESS QUESTIONS FOR AN ONLINE VIDEO RENTAL COMPANY
OBJECTIVE
Rockbuster Stealth LLC is a movie rental company that used to have stores around the world.
Facing stiff competition from streaming services such as Netflix and Amazon Prime, the Rockbuster Stealth
management team is planning to use its existing movie licenses to launch an online video rental service in order to
stay competitive.
DATA
Data set contains information about Rockbuster’s film inventory, customers, and payments, among other things.
-
It’s around 3MB and contains several files.
-
A relationship exists between two tables if a column name is present in both tables.
SKILLS
-
Relational databases
-
SQL
-
Database querying
-
Filtering
-
Cleaning and summarizing
-
Joining tables
-
Subqueries
-
Common table expressions
TECHNOLOQIES
-
PostgreSQL
-
DbVisualizer
-
Excel
-
Canva
ERD
The Rockbuster database appears to have a star schema. In a star schema, there's a central fact table (like the "Rental" table in this case) surrounded by dimension tables (such as "Actor," "Address," "Category," "Customer," "Film," "Inventory," "Language," "Payment,""Staff," etc.).
CUSTOMER LOCATIONS
The analysis of Rockbuster's customer base reveals a diverse and global reach, with customers spanning across various countries. The top countries contributing to Rockbuster's customer base include India, China, the United States, Japan, Mexico, Brazil, the Russian Federation, the Philippines, Turkey, and Indonesia.
HIGH LIFETIME VALUE CUSTOMERS
The top 10 cities from the top countries based on the highest number of customers. The results include cities such as Aurora in the United States with 2 customers, Atlixco in Mexico with 1 customer, Xintai in China with 1 customer, and others, along with the corresponding country and customer count for each city.
SELECT C.city, COUNT(customer_id) AS
customer_number
FROM customer A
JOIN address B ON A.address_id = B.address_id
JOIN city C ON B.city_id = C.city_id
JOIN country D ON C.country_id = D.country_id
WHERE D.country IN (
SELECT D.country
FROM customer A
JOIN address B ON A.address_id = B.address_id
JOIN city C ON B.city_id = C.city_id
JOIN country D ON C.country_ID = D.country_ID
GROUP BY D.country
ORDER BY COUNT(customer_id) DESC
LIMIT 10
)
GROUP BY C.city
ORDER BY customer_number DESC
LIMIT 10;
TOP 5 CUSTOMERS
The top 5 customers from the top 10 cities who’ve paid the highest total amounts to Rockbuster.
SELECT A.customer_id,
A.first_name,
A.last_name,
D.country,
C.city,
SUM (E.amount) AS total_amount_paid
FROM customer A
JOIN address B ON A.address_id = B.address_id
JOIN city C ON B.city_id = C.city_id
JOIN country D ON C.country_id = D.country_id
JOIN payment E ON A.customer_id = E.customer_id
WHERE D.country IN (
SELECT D.country
FROM customer A
JOIN address B ON A.address_id = B.address_id
JOIN city C ON B.city_id = C.city_id
JOIN country D ON C.country_ID = D.country_ID
GROUP BY D.country
ORDER BY COUNT(customer_id)
DESCLIMIT 10
)
GROUP BY A.customer_id, D.country, C.city_id
ORDER BY total_amount_paid
DESCLIMIT 5;