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