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


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;


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
168 Average number of streams in the US tiktok medium
169 Same day streams tiktok easy
170 Top 3 countries by average watch time tiktok easy