185. Top 5 non-English contents by watch time

medium netflix

  • ********************************5******************************************************************************************************************




Table 1: netflix_content

 col_name          | col_type
 content_id        | bigint
 content_type      | character varying(10) -- either TV or Movie
 original_language | character varying(2) -- 'EN',  'ES', 'CN', 'KO'
 release_date      | date

Table 2: netflix_daily_streaming

Daily aggregated watch time by account by content.

  col_name    | col_type
 date         | date
 account_id   | bigint
 content_id   | bigint
 duration     | int -- in seconds

Sample results

 content_id | watch_time_in_seconds
    1000224 |                 26808
    1000641 |                 24902
    1000638 |                 24627
    1000869 |                 21325
    1000580 |                 20884

Solution postgres

WITH non_english_content AS (
  SELECT content_id, release_date
  FROM netflix_content
  WHERE original_language <> 'EN'
SELECT N.content_id, SUM(duration) watch_time_in_seconds
FROM non_english_content N
INNER JOIN netflix_daily_streaming S
ON S.content_id = N.content_id
WHERE S.date <= N.release_date + 28


This query is selecting non-English Netflix content and calculating the total watch time in seconds for each content within the first 28 days of its release. The results are then grouped by content_id and ordered by watch time in descending order, with only the top five results being returned.

The first part of the query defines a temporary table non_english_content, which selects content_id and release_date from the netflix_content table where the original_language is not English.

The second part of the query uses an inner join to join the non_english_content table with the netflix_daily_streaming table on the content_id column. The WHERE clause limits the results to only those where the date in the daily_streaming table is within 28 days of the release date of the content in the non_english_content table.

The SELECT statement then calculates the total watch time in seconds for each content, grouping the results by content_id. Finally, the results are ordered in descending order by watch time and limited to the top five results.

Expected results

More Netflix questions

ID Title Level FTPR
181 Users who watched less than one hour of Netflix netflix easy
182 Top 5 content by number of watchers netflix medium
183 Top 5 movies by number of watchers netflix easy
184 Top 5 movies by watch time netflix medium