169. Same day streams

easy tiktok

  • *****************************************************************************************************************************************************************

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

(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';


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.

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
170 Top 3 countries by average watch time tiktok easy
171 Binge watches tiktok easy