- *****************************************************************************************************************************************************************
Table 1: subscription
When a user subscribes to amazon prime video.
col_name | col_type
-------------------+--------------------------
subscription_id | bigint
subscription_dt | date
customer_id | bigint
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
count
-------
6
(1 row)
Solution postgres
SELECT COUNT(stream_id)
FROM subscription S
INNER JOIN video_stream V
ON S.customer_id = V.customer_id
AND S.subscription_dt = V.stream_dt
WHERE stream_dt >= '2021-08-01'
AND stream_dt <= '2021-08-07';
Explanation
This query is counting the number of video streams that were subscribed to during the week of August 1st to August 7th in the year 2021. It does this by joining two tables, "subscription" and "video_stream", based on the customer ID and the subscription date matching the stream date. The result will show the total count of stream IDs that meet the specified criteria.
Copied
Expected results
Your 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 |
|
| 170 | Top 3 countries by average watch time tiktok | easy |
|
| 171 | Binge watches tiktok | easy |
|