Netflix Movies and TV Shows Analysis using PostgreSQL

A SQL project that answers 15 business questions on the Netflix titles dataset: content mix, ratings, countries, genres, directors, actors and release trends. It uses aggregations, CTEs, window functions, string and date functions, and array functions to turn a messy single table into insights.

Tools: PostgreSQL, SQL (pgAdmin or any PostgreSQL client) Dataset: Netflix Movies and TV Shows (netflix_titles.csv, Kaggle) [add the dataset link and row count]

---

Business questions answered

#	Question	SQL concepts used
1	How many Movies vs TV Shows are there	GROUP BY, COUNT
2	Most common rating for movies and for TV shows	CTE, RANK() OVER (PARTITION BY)
3	Movies added in a given year (2020)	EXTRACT, TO_DATE
4	Top 5 countries with the most content	GROUP BY, ORDER BY, LIMIT
5	The longest movie	SPLIT_PART, type casting, ORDER BY
6	Content added in the last 5 years	TO_DATE, date intervals
7	All titles by director 'Rajiv Chilaka'	WHERE filter
8	TV shows with more than 5 seasons	SPLIT_PART, casting
9	Number of titles in each genre	UNNEST, STRING_TO_ARRAY
10	Content added per year in India	Subquery, EXTRACT, AVG
11	All movies that are documentaries	LIKE pattern matching
12	Content without a director	IS NULL
13	Movies with 'Salman Khan' in the last 10 years	ILIKE, date intervals
14	Top 10 actors in Indian titles	UNNEST, STRING_TO_ARRAY, GROUP BY
15	Label content 'Good' or 'Bad' by keywords in the description	CASE, ILIKE, subquery

---

Dataset and table

One table, netflix:

Column	Type	Notes
show_id	VARCHAR(5)	Primary key
type	VARCHAR(10)	Movie or TV Show
title, director, casts, country	VARCHAR	casts, country and director can hold several comma-separated values
date_added	VARCHAR(55)	Stored as text, for example September 25, 2021
release_year	INT	Year the title was released
rating	VARCHAR(15)	For example TV-MA, PG-13
duration	VARCHAR(15)	90 min for movies, 2 Seasons for TV shows
listed_in	VARCHAR(250)	Comma-separated genres
description	VARCHAR(550)	Short summary

Data handling notes

• date_added is text, so queries convert it with TO_DATE(date_added, 'Month DD, YYYY') before filtering by year or interval.
• duration mixes minutes and seasons, so queries take the number with SPLIT_PART(duration, ' ', 1)::INT.
• casts, country and listed_in hold several values in one cell, so queries split them with UNNEST(STRING_TO_ARRAY(...)) before counting.

---

Repository contents

.
├── README.md
├── [your_sql_file].sql      # table creation, data load and all 15 queries
├── data/                    # netflix_titles.csv [if you add it]
└── images/                  # [result screenshots]

---

How to run

1. Install PostgreSQL and open pgAdmin (or psql).
2. Create a database and run the CREATE TABLE netflix (...) statement from the SQL file.
3. Load the data. In the SQL file, change the COPY path to where the CSV is on your machine, or use pgAdmin's Import/Export tool (or \copy in psql).
4. Run the queries one at a time; each has a comment with the question above it.

---

Sample queries

Most common rating for movies and for TV shows

WITH rating_counts AS (
    SELECT type, rating, COUNT(*) AS rating_count
    FROM netflix
    GROUP BY type, rating
),
ranked AS (
    SELECT type, rating, rating_count,
           RANK() OVER (PARTITION BY type ORDER BY rating_count DESC) AS rnk
    FROM rating_counts
)
SELECT type, rating AS most_frequent_rating
FROM ranked
WHERE rnk = 1;

Number of titles in each genre (splitting a comma-separated column)

SELECT TRIM(genre) AS genre, COUNT(*) AS total_content
FROM (
    SELECT UNNEST(STRING_TO_ARRAY(listed_in, ',')) AS genre
    FROM netflix
) AS g
GROUP BY 1
ORDER BY total_content DESC;

---

Key insights

[Fill these in from your own results. Examples of what to write: the Movie vs TV Show split, the most common rating for each type, the top countries, the most common genres, and how many titles have no director. Add one or two result screenshots in images/.]

---

What I learned

• Cleaning and querying text-based dates and durations in PostgreSQL.
• Splitting multi-value columns with UNNEST and STRING_TO_ARRAY to count genres, countries and actors.
• Ranking within groups using CTEs and window functions.
• Turning one wide table into business answers.

---

Acknowledgements

Dataset: Netflix Movies and TV Shows on Kaggle. Problem statements adapted from [name the tutorial or course you followed, with a link]. The SQL solutions are my own.

---

Author

Rahul Kumar: Python/Django backend developer with SQL experience LinkedIn: www.linkedin.com/in/rahulkumar350 | GitHub: RahulRanjan350