Netflix

SQL Project –

Source of Dataset: Unknown

Project: Determine the best combination of directors for a hit show

Phil Sgriccia is the director with the most amount of hit shows. He did not have a co-director therefore, he alone would be the best probability of having another hit show.

______________________________________________________________________________________

## Shows all shows including the directors1 ##

## Joins on the show_id from the titles and directors1 tables ##

  • SELECT titles.show_id,
  •     titles.title,
  •     directors1.director
  • FROM   mynetflix-415823.netflix.titles
  • INNER JOIN netflix.directors1 ON
  •     titles.show_id=directors1.show_id;

Number 2


## Show title from titles, listed_in from categories,director from directors1 all on primary key show_id ##

  • SELECT titles.show_id,
  •     titles.title,
  •     categories.listed_in,
  •     directors1.director
  • FROM mynetflix-415823.netflix.titles
  • INNER JOIN mynetflix-415823.netflix.categories
  • ON titles.show_id=categories.show_id
  • INNER JOIN mynetflix-415823.netflix.directors1
  • ON categories.show_id=directors1.show_id;

Number 3

## Same as above but also includes director from directors2 table ##

  • SELECT titles.title,
  •     titles.show_id,
  •     categories.listed_in,
  •     directors1.director,
  •     directors2.director
  • FROM mynetflix-415823.netflix.titles
  • INNER JOIN mynetflix-415823.netflix.categories
  • ON titles.show_id=categories.show_id
  • INNER JOIN mynetflix-415823.netflix.directors1
  • ON categories.show_id=directors1.show_id
  • INNER JOIN mynetflix-415823.netflix.directors2
  • ON directors2.show_id=categories.show_id;

Number 4

##Shows same as above but groups by titles, directors1, categories, and directors2##

SELECT COUNT(titles.title) AS Total,
titles.show_id,
categories.listed_in,
directors1.director,
directors2.director
FROM mynetflix-415823.netflix.titles
INNER JOIN mynetflix-415823.netflix.categories
ON titles.show_id=categories.show_id
INNER JOIN mynetflix-415823.netflix.directors1
ON categories.show_id=directors1.show_id
INNER JOIN mynetflix-415823.netflix.directors2
ON directors2.show_id=categories.show_id
GROUP BY titles.show_id,
categories.listed_in,
directors1.director,
directors2.director;

Number 5

## Count the number of shows in each rating ##

SELECT DISTINCT rating,
COUNT(details.rating) AS Total
FROM mynetflix-415823.netflix.details
GROUP BY details.rating;

Number 6


## Counts the number of ratings grouped by release_year and sorted by year ##

SELECT DISTINCT rating,
COUNT(rating) AS Total,
release_year
FROM mynetflix-415823.netflix.details
GROUP BY release_year,
rating
ORDER BY(release_year) ASC;

Number 7

##Shows all the shows where the rating is not NULL##

SELECT *
FROM mynetflix-415823.netflix.details
WHERE rating IS NOT NULL;

Number 8


## Number of shows by director in the directors1 table shown in descending order by number of shows ##

SELECT
directors1.director,
COUNT (title) as Count
FROM mynetflix-415823.netflix.titles
INNER JOIN netflix.directors1 ON
titles.show_id=directors1.show_id
GROUP BY directors1.director
ORDER BY Count DESC;

Number 9


## Table of directors1 and directors2 that have worked on the same shows and how many seasons the show lasted##

SELECT
details.show_id,
directors1.director,
directors2.director,
details.duration_seasons
FROM mynetflix-415823.netflix.details
JOIN mynetflix-415823.netflix.directors1
ON details.show_id=directors1.show_id
JOIN mynetflix-415823.netflix.directors2
ON directors1.show_id=directors2.show_id
WHERE details.duration_seasons IS NOT NULL
ORDER BY details.duration_seasons DESC;

Number 10

## Show director1 and director2 combinations to show how many shows each worked on together##

SELECT
details.show,
directors1.director,
directors2.director,
SUM(details.duration_seasons) AS total_duration_seasons
FROM mynetflix-415823.netflix.details
JOIN mynetflix-415823.netflix.directors1
ON details.show=directors1.show_id
JOIN mynetflix-415823.netflix.directors2
ON directors1.show_id=directors2.show_id
WHERE
details.duration_seasons IS NOT NULL
GROUP BY
directors1.director,
directors2.director,
details.show
HAVING total_duration_seasons > 1
ORDER BY
total_duration_seasons DESC;

Number 11

##Shows all shows including the directors1##

##Joins on the show_id from the titles and directors1 tables##

SELECT titles.show_id,
titles.title,
directors1.show_id,
directors1.director
FROM mynetflix-415823.netflix.titles
INNER JOIN netflix.directors1 ON
titles.show_id=directors1.show_id;

Number 12

##Show title from titles, listed_in from categories##

##director from directors1 all on primary key show_id##

SELECT titles.show_id,
titles.title,
categories.listed_in,
directors1.director
FROM mynetflix-415823.netflix.titles
INNER JOIN mynetflix-415823.netflix.categories
ON titles.show_id=categories.show_id
INNER JOIN mynetflix-415823.netflix.directors1
ON categories.show_id=directors1.show_id;

Number 13

##Same as above but also includes director from directors2 table##

SELECT titles.title,
titles.show_id,
details.show_id,
categories.listed_in,
directors1.director,
directors2.director
FROM mynetflix-415823.netflix.titles
INNER JOIN mynetflix-415823.netflix.categories
ON titles.show_id=categories.show_id
INNER JOIN mynetflix-415823.netflix.directors1
ON categories.show_id=directors1.show_id
INNER JOIN mynetflix-415823.netflix.directors2
ON directors2.show_id=categories.show_id
INNER JOIN mynetflix-415823.netflix.details
ON details.show_id=titles.show_id;

Number 14


##Shows same as above but groups by directors1, categories, and directors2##

SELECT COUNT(titles.title) AS Total,
titles.show_id,
categories.listed_in,
directors1.director,
directors2.director
FROM mynetflix-415823.netflix.titles
INNER JOIN mynetflix-415823.netflix.categories
ON titles.show_id=categories.show_id
INNER JOIN mynetflix-415823.netflix.directors1
ON categories.show_id=directors1.show_id
INNER JOIN mynetflix-415823.netflix.directors2
ON directors2.show_id=categories.show_id
GROUP BY titles.show_id,
categories.listed_in,
directors1.director,
directors2.director;