DVD Rental Store Analysis (SQL Portfolio Project)

Project Overview

This project explores a DVD rental database using SQL to answer a series of increasingly complex business questions.
The goal is to demonstrate SQL skills, from simple queries (DISTINCT) to advanced concepts like subqueries, CASE statements, JOINs, and correlated queries.

---

Objectives

• Showcase SQL query writing across multiple difficulty levels.
• Extract business insights about customers, films, revenue, and categories.
• Demonstrate data analysis techniques useful in real-world scenarios.

---

Tools & Technologies

• SQL (tested in PostgreSQL)

---

Project Structure

• queries/ – contains SQL scripts for each question
• analysis.md – answers with SQL code and explanations
• README.md – project overview

---

Key Questions & Insights

1. DISTINCT values

• Task: List all distinct replacement costs of the films.
• Insight: Lowest replacement cost is 9.99.

2. CASE + GROUP BY

• Task: Classify films into low, medium, high replacement cost ranges.
• Insight: 514 films fall into the "low" group.

3. JOIN (Films & Categories)

• Task: Find the longest film in Drama or Sports.
• Insight: Longest film is in Sports, with a length of 184 minutes.

4. JOIN + GROUP BY (Film Categories)

• Task: Count how many films are in each category.
• Insight: Sports is the most common with 74 titles.

5. JOIN + GROUP BY (Actors)

• Task: Count how many films each actor appears in.
• Insight: Susan Davis appears in the most films (54).

6. LEFT JOIN & Filtering

• Task: Find addresses with no associated customers.
• Insight: There are 4 addresses with no customer.

7. JOIN + GROUP BY (Sales by City)

• Task: Find the city with the most sales (by customer location).
• Insight: Cape Coral with 221.55 total sales.

8. JOIN + GROUP BY (Revenue by Country/City)

• Task: Calculate revenue grouped by country, city.
• Insight: Lowest sales: United States, Tallahassee (50.85).

9. Uncorrelated Subquery (Staff Performance)

• Task: Average revenue per customer for each staff.
• Insight: Staff ID 2 has the higher average (56.64).

10. Extract + Subquery (Average Sunday Revenue)

• Task: Calculate daily average revenue for all Sundays.
• Insight: 1410.65.

11. Correlated Subquery (Films Above Average Length per Cost)

• Task: List films longer than the average in their replacement cost group.
• Insight: Shortest on this list are Celebrity Horn and Seattle Expectations (110 minutes).

12. Customer Lifetime Value (Districts)

• Task: Average customer lifetime value grouped by district.
• Insight: Highest is Saint-Denis (216.54).

13. Window Function (Payments with Category Totals)

• Task: Payments with category and total category revenue.
• Insight: Action total revenue is 4375.85, lowest payment_id is 16055.

14. CTEs with Window Functions for Ranking (Top Film per Category)

• Task: Find the top revenue film in each category.
• Insight: Best in Animation is Dogma Family (178.70).

---

Results

• Demonstrated SQL skills from beginner to advanced.
• Extracted actionable business insights from a DVD rental database.
• Organized the project for portfolio showcase.