- *****************************************************************************************************************************************
- *****************************************
Table 1: dates
Calendar dates from 01/01/2019 to 12/31/2025.
col_name | col_type ----------+---------- year | smallint month | smallint date | date
Table 2: 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
date | num_streams
------------+-------------
2021-08-01 | 2
2021-08-02 | 0
2021-08-03 | 3
2021-08-04 | 0
2021-08-05 | 3
2021-08-06 | 1
2021-08-07 | 0
Solution postgres
SELECT D.date, COUNT(stream_id) AS num_streams
FROM dates D
LEFT JOIN video_stream V
ON D.date = V.stream_dt
AND customer_country = 'US'
WHERE D.date >= '2021-08-01'
AND D.date <= '2021-08-07'
GROUP BY D.date;
Explanation
This query is counting the number of video streams that happened in the US for each day between August 1st and August 7th of 2021. It starts by selecting the date column from a table called "dates" and then joins it with a table called "video_stream" on the condition that the date in the "dates" table matches the "stream_dt" column in the "video_stream" table, and that the customer country is the US.
Next, it filters the results to only include dates between August 1st and August 7th, and groups the results by date. Finally, it counts the number of stream IDs for each date, and labels that count as "num_streams".
In summary, this query is counting the number of video streams that happened in the US for each day between August 1st and August 7th of 2021.
Expected results
More Tiktok questions
| ID | Title | Level | FTPR |
|---|---|---|---|
| 167 | Top country in video streaming tiktok | easy |
|
| 169 | Same day streams tiktok | easy |
|
| 170 | Top 3 countries by average watch time tiktok | easy |
|
| 171 | Binge watches tiktok | easy |
|