-
Notifications
You must be signed in to change notification settings - Fork 0
/
Netflix Analytics
94 lines (81 loc) · 8 KB
/
Netflix Analytics
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
# This project reveals insights about TV and Movie content hosted on Netflix.
# The project was written using the PostgreSQL dialect and utilized data from the following two Netflix datasets:
CREATE TABLE netflix_titles_info
("show_id" varchar(5), "type" varchar(7), "title" varchar(58), "country" varchar(74), "date_added" timestamp, "release_year" int, "rating" varchar(5), "duration" varchar(9), "listed_in" varchar(74))
;
CREATE TABLE netflix_people
("show_id" varchar(5), "director" varchar(54), "cast" varchar(532))
;
INSERT INTO netflix_titles_info
("show_id", "type", "title", "country", "date_added", "release_year", "rating", "duration", "listed_in")
VALUES
('s1', 'Movie', 'Dick Johnson Is Dead', 'United States', '2021-09-25 00:00:00', 2020, 'PG-13', '90 min', 'Documentaries'),
('s2', 'TV Show', 'Blood & Water', 'South Africa', '2021-09-24 00:00:00', 2021, 'TV-MA', '2 Seasons', 'International TV Shows | TV Dramas | TV Mysteries'),
('s3', 'TV Show', 'Ganglands', NULL, '2021-09-24 00:00:00', 2021, 'TV-MA', '1 Season', 'Crime TV Shows | International TV Shows | TV Action & Adventure'),
('s4', 'TV Show', 'Jailbirds New Orleans', NULL, '2021-09-24 00:00:00', 2021, 'TV-MA', '1 Season', 'Docuseries | Reality TV'),
('s5', 'TV Show', 'Kota Factory', 'India', '2021-09-24 00:00:00', 2021, 'TV-MA', '2 Seasons', 'International TV Shows | Romantic TV Shows | TV Comedies'),
('s6', 'TV Show', 'Midnight Mass', NULL, '2021-09-24 00:00:00', 2021, 'TV-MA', '1 Season', 'TV Dramas | TV Horror | TV Mysteries'),
('s7', 'Movie', 'My Little Pony: A New Generation', NULL, '2021-09-24 00:00:00', 2021, 'PG', '91 min', 'Children & Family Movies'),
('s8', 'Movie', 'Sankofa', 'United States | Ghana | Burkina Faso | United Kingdom | Germany | Ethiopia', '2021-09-24 00:00:00', 1993, 'TV-MA', '125 min', 'Dramas | Independent Movies | International Movies'),
('s9', 'TV Show', 'The Great British Baking Show', 'United Kingdom', '2021-09-24 00:00:00', 2021, 'TV-14', '9 Seasons', 'British TV Shows | Reality TV'),
('s10', 'Movie', 'The Starling', 'United States', '2021-09-24 00:00:00', 2021, 'PG-13', '104 min', 'Comedies | Dramas'),
('s11', 'TV Show', 'Vendetta: Truth Lies and The Mafia', NULL, '2021-09-24 00:00:00', 2021, 'TV-MA', '1 Season', 'Crime TV Shows | Docuseries | International TV Shows'),
('s12', 'TV Show', 'Bangkok Breaking', NULL, '2021-09-23 00:00:00', 2021, 'TV-MA', '1 Season', 'Crime TV Shows | International TV Shows | TV Action & Adventure'),
('s13', 'Movie', 'Je Suis Karl', 'Germany | Czech Republic', '2021-09-23 00:00:00', 2021, 'TV-MA', '127 min', 'Dramas | International Movies'),
('s14', 'Movie', 'Confessions of an Invisible Girl', NULL, '2021-09-22 00:00:00', 2021, 'TV-PG', '91 min', 'Children & Family Movies | Comedies'),
('s15', 'TV Show', 'Crime Stories: India Detectives', NULL, '2021-09-22 00:00:00', 2021, 'TV-MA', '1 Season', 'British TV Shows | Crime TV Shows | Docuseries'),
('s16', 'TV Show', 'Dear White People', 'United States', '2021-09-22 00:00:00', 2021, 'TV-MA', '4 Seasons', 'TV Comedies | TV Dramas'),
('s17', 'Movie', 'Europe''s Most Dangerous Man: Otto Skorzeny in Spain', NULL, '2021-09-22 00:00:00', 2020, 'TV-MA', '67 min', 'Documentaries | International Movies'),
('s18', 'TV Show', 'Falsa identidad', 'Mexico', '2021-09-22 00:00:00', 2020, 'TV-MA', '2 Seasons', 'Crime TV Shows | Spanish-Language TV Shows | TV Dramas'),
('s19', 'Movie', 'Intrusion', NULL, '2021-09-22 00:00:00', 2021, 'TV-14', '94 min', 'Thrillers'),
('s20', 'TV Show', 'Jaguar', NULL, '2021-09-22 00:00:00', 2021, 'TV-MA', '1 Season', 'International TV Shows | Spanish-Language TV Shows | TV Action & Adventure')
;
INSERT INTO netflix_people
("show_id", "director", "cast")
VALUES
('s1', 'Kirsten Johnson', NULL),
('s2', NULL, 'Ama Qamata| Khosi Ngema| Gail Mabalane| Thabang Molaba| Dillon Windvogel| Natasha Thahane| Arno Greeff| Xolile Tshabalala| Getmore Sithole| Cindy Mahlangu| Ryle De Morny| Greteli Fincham| Sello Maake Ka-Ncube| Odwa Gwanya| Mekaila Mathys| Sandi Schultz| Duane Williams| Shamilla Miller| Patrick Mofokeng'),
('s3', 'Julien Leclercq', 'Sami Bouajila| Tracy Gotoas| Samuel Jouy| Nabiha Akkari| Sofia Lesaffre| Salim Kechiouche| Noureddine Farihi| Geert Van Rampelberg| Bakary Diombera'),
('s4', NULL, NULL),
('s5', NULL, 'Mayur More| Jitendra Kumar| Ranjan Raj| Alam Khan| Ahsaas Channa| Revathi Pillai| Urvi Singh| Arun Kumar'),
('s6', 'Mike Flanagan', 'Kate Siegel| Zach Gilford| Hamish Linklater| Henry Thomas| Kristin Lehman| Samantha Sloyan| Igby Rigney| Rahul Kohli| Annarah Cymone| Annabeth Gish| Alex Essoe| Rahul Abburi| Matt Biedel| Michael Trucco| Crystal Balint| Louis Oliver'),
('s7', 'Robert Cullen| José Luis Ucha', 'Vanessa Hudgens| Kimiko Glenn| James Marsden| Sofia Carson| Liza Koshy| Ken Jeong| Elizabeth Perkins| Jane Krakowski| Michael McKean| Phil LaMarr'),
('s8', 'Haile Gerima', 'Kofi Ghanaba| Oyafunmike Ogunlano| Alexandra Duah| Nick Medley| Mutabaruka| Afemo Omilami| Reggie Carter| Mzuri'),
('s9', 'Andy Devonshire', 'Mel Giedroyc| Sue Perkins| Mary Berry| Paul Hollywood'),
('s10', 'Theodore Melfi', 'Melissa McCarthy| Chris O''Dowd| Kevin Kline| Timothy Olyphant| Daveed Diggs| Skyler Gisondo| Laura Harrier| Rosalind Chao| Kimberly Quinn| Loretta Devine| Ravi Kapoor'),
('s11', NULL, NULL),
('s12', 'Kongkiat Komesiri', 'Sukollawat Kanarot| Sushar Manaying| Pavarit Mongkolpisit| Sahajak Boonthanakit| Suthipongse Thatphithakkul| Bhasaworn Bawronkirati| Daweerit Chullasapya| Waratthaya Wongchayaporn| Kittiphoom Wongpentak| Abhicha Thanachanun| Nophand Boonyai| Kittipong Khamsat| Arisara Wongchalee| Jaytiya Naiwattanakul| Pantipa Arunwattanachai| Panupan Jantanawong| Kungtap Saelim| Phumphat Chartsuriyakiat| Issara Veranitinunt| Keerati Sivakuae| Panjai Sirisuwan| Supranee Charoenpol| Suda Chuenban| Visaka Banhansupavat| Pitchatorn Santinatornkul'),
('s13', 'Christian Schwochow', 'Luna Wedler| Jannis Niewöhner| Milan Peschel| Edin Hasanović| Anna Fialová| Marlon Boess| Victor Boccard| Fleur Geffrier| Aziz Dyab| Mélanie Fouché| Elizaveta Maximová'),
('s14', 'Bruno Garotti', 'Klara Castanho| Lucca Picon| Júlia Gomes| Marcus Bessa| Kiria Malheiros| Fernanda Concon| Gabriel Lima| Caio Cabral| Leonardo Cidade| Jade Cardozo'),
('s15', NULL, NULL),
('s16', NULL, 'Logan Browning| Brandon P. Bell| DeRon Horton| Antoinette Robertson| John Patrick Amedori| Ashley Blaine Featherson| Marque Richardson| Giancarlo Esposito'),
('s17', 'Pedro de Echave GarcÃa| Pablo AzorÃn Williams', NULL),
('s18', NULL, 'Luis Ernesto Franco| Camila Sodi| Sergio Goyri| Samadhi Zendejas| Eduardo Yáñez| Sonya Smith| Alejandro Camacho| Azela Robinson| Uriel del Toro| Géraldine Bazán| Gabriela Roel| Marcus Ornellas'),
('s19', 'Adam Salky', 'Freida Pinto| Logan Marshall-Green| Robert John Burke| Megan Elisabeth Kelly| Sarah Minnich| Hayes Hargrove| Mark Sivertsen| Brandon Fierro| Antonio Valles| Clint Obenchain'),
('s20', NULL, 'Blanca Suárez| Iván Marcos| Óscar Casas| Adrián Lastra| Francesc Garrido| Stefan Weinert| Julia Möller| Alicia Chojnowski')
;
# The following SQL was written to answer specific questions about the content on Netflix:
#1. How many movie titles are there in the database? (movies only, not tv shows)
select count(*)
FROM "netflix_titles_info"
WHERE type='Movie';
#2. When was the most recent batch of tv shows and/or movies added to the database?
select max(date(date_added))
FROM "netflix_titles_info";
#3. List all the movies and tv shows in alphabetical order.
select title
FROM "netflix_titles_info"
ORDER BY title asc;
#4. Who was the Director for the movie The Starling?
select
director
FROM "netflix_titles_info" titles
LEFT JOIN "netflix_people" people
ON titles.show_id=people.show_id
where titles.title='The Starling'
#5. What is the oldest movie in the database and what year was it made?
select title, release_year
FROM "netflix_titles_info"
WHERE type='Movie'
ORDER BY release_year asc
LIMIT 1;