Netflix Movie Watch Hours

Exploring user watch hours of netflix movies and tv shows both globally and in the US

DataFrameas

df

variable

-- Weekly hours watched of the #1 movie on netflix globally
SELECT 
	ROW_NUMBER() OVER(ORDER BY week) as week_number,
	show_title,
	weekly_hours_viewed/10000 as "weekly_hours_viewed/10000"
FROM public.all_weeks_global
WHERE weekly_rank = 1
	AND category = 'Films (English)'
LIMIT 52;

import plotly.express as px


fig = px.line(df, x='week_number', y='weekly_hours_viewed/10000', markers=True, line_shape='linear', hover_data=['show_title'])

# Customize axis labels and title
fig.update_layout(
    xaxis_title='Week Number',
    yaxis_title='Weekly Hours Viewed (in 10000s)',
    title='Weekly Hours Viewed Over Time For The #1 Movie'
)

fig.update_traces(hovertemplate='Title: %{customdata[0]}')

# Show the plot
fig.show()

DataFrameas

df1

variable

-- Weekly hours watched of the #1 tv show
SELECT
	ROW_NUMBER() OVER(ORDER BY week) as week_number,
	show_title,
	weekly_hours_viewed/10000 as "weekly_hours_viewed/10000"
FROM public.all_weeks_global
WHERE weekly_rank = 1
	AND category = 'TV (English)'
LIMIT 52;

import plotly.express as px


fig = px.line(df1, x='week_number', y='weekly_hours_viewed/10000', markers=True, line_shape='linear', hover_data=['show_title'])

# Customize axis labels and title
fig.update_layout(
    xaxis_title='Week Number',
    yaxis_title='Weekly Hours Viewed (in 10000s)',
    title='Weekly Hours Viewed Over Time For The #1 TV Show'
)

fig.update_traces(hovertemplate='Title: %{customdata[0]}')
fig.update_traces(line_color = 'orange')

# Show the plot
fig.show()

import plotly.express as px

# Add line from df aka Movies
fig = px.line(df, x='week_number', y='weekly_hours_viewed/10000', markers=True, line_shape='linear', hover_data=['show_title'])

# Add a second line from df1 aka Shows
fig.add_trace(px.line(df1, x='week_number', y='weekly_hours_viewed/10000', markers=True, line_shape='linear', hover_data=['show_title']).data[0])

# Update the trace colors
fig.data[0].line.color = 'blue'
fig.data[1].line.color = 'orange'

# Customize axis labels and title
fig.update_layout(
    xaxis_title='Week Number',
    yaxis_title='Weekly Hours Viewed (in 10000s)',
    title='Weekly Hours Viewed Over Time'
)

# Customize the hover template for both lines
fig.update_traces(hovertemplate='Title: %{customdata[0]}')


# Show the plot
fig.show()

There is no correlation between the time people spend watching the number 1 TV show and number 1 movie

DataFrameas

df2

variable

-- Weekly watch hours of the top 10 movies and top 10 tv shows
WITH topmovie as(
	SELECT 
		ROW_NUMBER() OVER(ORDER BY week) as week_number,
		sum(weekly_hours_viewed)/10000 as "weekly_movie_hours_viewed/10000"
	FROM public.all_weeks_global
	WHERE category = 'Films (English)'
	GROUP BY week
	LIMIT 52),
toptv as(
	SELECT 
		ROW_NUMBER() OVER(ORDER BY week) as week_number,
		sum(weekly_hours_viewed)/10000 as "weekly_tv_hours_viewed/10000"
	FROM public.all_weeks_global
	WHERE category = 'TV (English)'
	GROUP BY week
	LIMIT 52),
month as(
	SELECT 
		ROW_NUMBER() OVER(ORDER BY week) as week_number,
		EXTRACT(month from week::date) as month
	FROM public.all_weeks_global
	GROUP BY week
	LIMIT 52)
	
	
SELECT
	m.week_number,
	"weekly_tv_hours_viewed/10000",
	"weekly_movie_hours_viewed/10000",
	month
FROM topmovie as m
LEFT JOIN toptv as t
USING(week_number)
LEFT JOIN month
USING(week_number)
ORDER BY week_number;

import plotly.express as px

fig = px.line(df2, x='week_number', y=['weekly_tv_hours_viewed/10000', 'weekly_movie_hours_viewed/10000'], markers=True, line_shape='linear')

# Customize axis labels and title
fig.update_layout(
    xaxis_title='Week Number',
    yaxis_title='Weekly Hours Viewed (in 10000s)',
    title='Weekly TV and Movie Hours Viewed Over Time'
)

# Show the plot
fig.show()

DataFrameas

df3

variable

WITH us AS(SELECT 
	country_iso2 as country,
	week,
	category,
	weekly_rank,
	show_title
FROM all_weeks_countries
WHERE country_iso2 = 'US'),

gb AS(SELECT 
	country_iso2 as country,
	week,
	category,
	weekly_rank,
	show_title
FROM all_weeks_countries
WHERE country_iso2 = 'GB')

SELECT 
	us.week as week,
	us.category,
	us.weekly_rank,
	us.show_title as US_show_title,
	gb.show_title as GB_show_title
FROM us
LEFT JOIN gb
ON us.week = gb.week
	AND us.category = gb.category
	AND us.weekly_rank = gb.weekly_rank
ORDER BY week,category,weekly_rank
LIMIT 10