SQL Portfolio Project

Overview

This project analyzes the PostgreSQL Pagila sample database, a DVD rental database containing information about customers, films, rentals, payments, stores, actors, and categories.

The goal of this project is to demonstrate SQL analytical skills by answering business questions related to revenue performance, customer behavior, film popularity, and rental activity.

The analysis was performed using PostgreSQL and focuses on transforming raw relational data into meaningful business insights.

---

Business Questions

This project answers questions across five main analytical areas:

Revenue Analysis

• What is the total revenue generated by each store?
• How has monthly revenue changed over time?
• Which film categories generate the most revenue?

Customer Behavior Analysis

• Who are the top customers by total spending?
• Which customers have been inactive for the longest time?
• Who are the highest-spending customers within each store?
• How are customers distributed into spending quartiles?

Film Performance Analysis

• Which films are rented the most?
• Which film generates the highest rental revenue within each category?
• Which actors appear in the most rented films?

Rental Activity Analysis

• Which days of the week and hours of the day experience the highest rental activity?

Revenue Trend Analysis

• How has monthly revenue changed compared to the previous month?

More details about each business question can be found in business_questions.md.

---

Tools & Technologies

• PostgreSQL
• SQL
• DBeaver
• Git & GitHub
• Visual Studio Code
• SQLTools

SQL techniques used:

• Joins
• Aggregations
• Common Table Expressions (CTEs)
• Window functions
• Ranking functions
• Date/time functions

---

Database Schema

The project uses the Pagila relational database.

The main analytical tables include:

• customer
• payment
• rental
• inventory
• film
• category
• film_category
• actor
• film_actor
• store
• staff

The complete schema documentation and entity relationship diagram are available in:

schema.md

---

How to Run This Project

This project uses the Pagila sample database (PostgreSQL).

1. Get Pagila — clone or download the schema and data files from xzilla/pagila:

   • pagila-schema.sql
   • pagila-data.sql

2. Create a database and load the files, in order:

   createdb pagila
   psql -d pagila -f pagila-schema.sql
   psql -d pagila -f pagila-data.sql

3. Verify the load:

   SELECT COUNT(*) FROM store;    -- expect 2
   SELECT COUNT(*) FROM customer; -- expect ~599
   SELECT COUNT(*) FROM film;     -- expect 1000

4. Run any query in the queries/ folder against the pagila database using your client of choice (psql, DBeaver, etc.). Each file is self-contained and includes its business question, context, and findings in the header comment.

---

Query Results

Screenshots of each query's output are available in:

results.md

---

Methodology

The analysis workflow consisted of:

1. Understanding the database structure
2. Identifying relevant business questions
3. Writing SQL queries to answer each question
4. Validating query outputs
5. Documenting analytical methods and results

Detailed methodology:

methodology.md

---

Key Skills Demonstrated

This project demonstrates experience with:

• Relational database analysis
• Writing analytical SQL queries
• Data aggregation and transformation
• Business-oriented problem solving
• Working with normalized database schemas
• Communicating analytical results through documentation