SQLPad
Sign In

171. Binge watches

easy tiktok
  • *********************************************
  • *********************************************************************************

Table: video_stream

Online movie streaming logs.

       col_name       |  col_type
----------------------+--------------------------
 stream_id            | bigint
 stream_dt            | date
 device_id            | integer
 device_category      | varchar(20) -- 'fire tv',  'apple tv', 'samsung tv', 'google tv', 'roku'
 customer_id          | bigint
 minutes_streamed     | integer
 buffer_count         | integer -- number of seconds before the video start playing
 customer_country     | varchar(20) -- US, UK, AU

Sample results 

 customer_id
-------------
       80002

Solution postgres

WITH watch_hours AS (
    SELECT customer_id,  SUM(minutes_streamed) * 1.0 / 60 AS total_watch_hour
    FROM  video_stream
    WHERE customer_country = 'US'
    AND stream_dt >= '2021-08-01'
    AND stream_dt <= '2021-08-31'
    GROUP BY customer_id
)
SELECT customer_id
FROM watch_hours
WHERE total_watch_hour > 5;

Explanation

This query is selecting data from a table called video_stream, and it's only selecting data from the United States during the month of August 2021. It's also grouping the data by customer_id and adding up the total watch time in hours for each customer.

The query then creates a temporary table called watch_hours with the total watch hours for each customer, and it selects the customer_id from that table where the total watch hour is greater than 5.

So, this query is finding all customers in the US who watched more than 5 hours of video in August 2021.

Expected results


More Tiktok questions

ID Title Level FTPR
167 Top country in video streaming tiktok easy
28%
168 Average number of streams in the US tiktok medium
13%
169 Same day streams tiktok easy
34%
170 Top 3 countries by average watch time tiktok easy
11%