-
********************************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
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5;
Explanation
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