170. Top 3 countries by average watch time

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_country |       avg_time
 AU               | 196.5000000000000000
 US               | 141.6000000000000000
 CA               |  84.0000000000000000

Solution postgres

SELECT customer_country, SUM(minutes_streamed) * 1.0 / COUNT(DISTINCT customer_id) AS avg_time
FROM video_stream
GROUP BY customer_country


This query retrieves data from a table called "video_stream" and calculates the average time customers from each country spend streaming videos. The result is sorted in descending order by the second column and limited to three rows.

The query starts by selecting the "customer_country" and the calculated average time as "avg_time". The calculation of the average time is done by dividing the sum of "minutes_streamed" by the count of distinct "customer_id" for each country.

The data is then grouped by "customer_country" to aggregate the results by country.

The results are then sorted in descending order by the second column "avg_time" and limited to the top three rows. This means that the top three countries with the highest average streaming time will be returned in the result set.

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
171 Binge watches tiktok easy