Netflix_SQL_Analysis.sql

-- Netflix Titles + Credits Analysis (PostgreSQL)
-- Tables: shows_movies.titles (id, title, type, description, release_year,
--         age_certification, runtime, genres, production_countries, seasons,
--         imdb_id, imdb_score, imdb_votes, tmdb_popularity, tmdb_score)
--         shows_movies.credits (person_id, id, name, character, role)

-- What were the top 10 movies according to IMDB score?
SELECT title,
type,
imdb_score
FROM shows_movies.titles
WHERE imdb_score >= 8.0
AND type = 'MOVIE'
ORDER BY imdb_score DESC
LIMIT 10;

-- What were the top 10 shows according to IMDB score?
SELECT title,
type,
imdb_score
FROM shows_movies.titles
WHERE imdb_score >= 8.0
AND type = 'SHOW'
ORDER BY imdb_score DESC
LIMIT 10;

-- What were the bottom 10 movies according to IMDB score?
SELECT title,
type,
imdb_score
FROM shows_movies.titles
WHERE type = 'MOVIE'
AND imdb_score IS NOT NULL
ORDER BY imdb_score ASC
LIMIT 10;

-- What were the bottom 10 shows according to IMDB score?
SELECT title,
type,
imdb_score
FROM shows_movies.titles
WHERE type = 'SHOW'
AND imdb_score IS NOT NULL
ORDER BY imdb_score ASC
LIMIT 10;

-- What were the average IMDB and TMDB scores for shows and movies?
SELECT type,
ROUND(AVG(imdb_score)::numeric,2) AS avg_imdb_score,
ROUND(AVG(tmdb_score)::numeric,2) AS avg_tmdb_score
FROM shows_movies.titles
GROUP BY type;

-- Count of movies and shows in each decade
SELECT CONCAT(FLOOR(release_year / 10) * 10, 's') AS decade,
    COUNT(*) AS movies_shows_count
FROM shows_movies.titles
WHERE release_year >= 1940
GROUP BY CONCAT(FLOOR(release_year / 10) * 10, 's')
ORDER BY decade;

-- What were the average IMDB and TMDB scores for each production country?
SELECT production_countries,
ROUND(AVG(imdb_score)::numeric,2) AS avg_imdb_score,
ROUND(AVG(tmdb_score)::numeric,2) AS avg_tmdb_score
FROM shows_movies.titles
WHERE production_countries IS NOT NULL AND production_countries != ''
GROUP BY production_countries
ORDER BY avg_imdb_score DESC;

-- What were the average IMDB and TMDB scores for each age certification?
SELECT age_certification,
ROUND(AVG(imdb_score)::numeric,2) AS avg_imdb_score,
ROUND(AVG(tmdb_score)::numeric,2) AS avg_tmdb_score
FROM shows_movies.titles
GROUP BY age_certification
ORDER BY avg_imdb_score DESC;

-- What were the 5 most common age certifications for movies?
SELECT age_certification,
COUNT(*) AS certification_count
FROM shows_movies.titles
WHERE type = 'MOVIE'
AND age_certification IS NOT NULL AND age_certification != ''
GROUP BY age_certification
ORDER BY certification_count DESC
LIMIT 5;

-- Who were the top 20 actors that appeared the most in movies/shows?
SELECT name AS actor,
COUNT(*) AS number_of_appearances
FROM shows_movies.credits
WHERE role = 'ACTOR'
GROUP BY name
ORDER BY number_of_appearances DESC
LIMIT 20;

-- Who were the top 20 directors that directed the most movies/shows?
SELECT name AS director,
COUNT(*) AS number_of_appearances
FROM shows_movies.credits
WHERE role = 'DIRECTOR'
GROUP BY name
ORDER BY number_of_appearances DESC
LIMIT 20;

-- Calculating the average runtime of movies and TV shows separately
SELECT
'Movies' AS content_type,
ROUND(AVG(runtime)::numeric,2) AS avg_runtime_min
FROM shows_movies.titles
WHERE type = 'MOVIE'
UNION ALL
SELECT
'Show' AS content_type,
ROUND(AVG(runtime)::numeric,2) AS avg_runtime_min
FROM shows_movies.titles
WHERE type = 'SHOW';

-- Finding the titles and directors of movies released on or after 2010
SELECT DISTINCT t.title, c.name AS director,
t.release_year
FROM shows_movies.titles AS t
JOIN shows_movies.credits AS c
ON t.id = c.id
WHERE t.type = 'MOVIE'
AND t.release_year >= 2010
AND c.role = 'DIRECTOR'
ORDER BY t.release_year DESC;

-- Which shows on Netflix have the most seasons?
SELECT title,
seasons
FROM shows_movies.titles
WHERE type = 'SHOW'
ORDER BY seasons DESC
LIMIT 10;

-- Which genres had the most movies?
SELECT genres,
COUNT(*) AS title_count
FROM shows_movies.titles
WHERE type = 'MOVIE'
GROUP BY genres
ORDER BY title_count DESC
LIMIT 10;

-- Which genres had the most shows?
SELECT genres,
COUNT(*) AS title_count
FROM shows_movies.titles
WHERE type = 'SHOW'
GROUP BY genres
ORDER BY title_count DESC
LIMIT 10;

-- Titles and directors of movies with high IMDB scores (>7.5) and high TMDB popularity (>80)
SELECT t.title,
c.name AS director
FROM shows_movies.titles AS t
JOIN shows_movies.credits AS c
ON t.id = c.id
WHERE t.type = 'MOVIE'
AND t.imdb_score > 7.5
AND t.tmdb_popularity > 80
AND c.role = 'DIRECTOR';

-- What were the total number of titles for each year?
SELECT release_year,
COUNT(*) AS title_count
FROM shows_movies.titles
GROUP BY release_year
ORDER BY release_year DESC;

-- Actors who have starred in the most highly rated movies or shows
SELECT c.name AS actor,
COUNT(*) AS num_highly_rated_titles
FROM shows_movies.credits AS c
JOIN shows_movies.titles AS t
ON c.id = t.id
WHERE c.role = 'ACTOR'
AND t.imdb_score > 8.0
AND t.tmdb_score > 8.0
GROUP BY c.name
ORDER BY num_highly_rated_titles DESC;

-- Which actors played the same character across multiple titles?
SELECT c.name AS actor,
c.character,
COUNT(DISTINCT t.title) AS num_titles
FROM shows_movies.credits AS c
JOIN shows_movies.titles AS t
ON c.id = t.id
WHERE c.role = 'ACTOR'
GROUP BY c.name, c.character
HAVING COUNT(DISTINCT t.title) > 1
ORDER BY num_titles DESC;

-- What were the top 3 most common genres overall?
SELECT genres,
COUNT(*) AS genre_count
FROM shows_movies.titles
WHERE type = 'MOVIE'
GROUP BY genres
ORDER BY genre_count DESC
LIMIT 3;

-- Average IMDB score per actor (actors with 3+ titles, to avoid one-hit skew)
SELECT c.name AS actor,
ROUND(AVG(t.imdb_score)::numeric,2) AS average_imdb_score,
COUNT(*) AS num_titles
FROM shows_movies.credits AS c
JOIN shows_movies.titles AS t
ON c.id = t.id
WHERE c.role = 'ACTOR'
GROUP BY c.name
HAVING COUNT(*) >= 3
ORDER BY average_imdb_score DESC
LIMIT 15;