- ********************************************************************************************************************************
- ***************************************************.
- ************************************************
Table: video_session
Video watch history for all users.
col_name | col_type -----------------+--------------------- session_id | bigint user_id | bigint video_id | bigint video_category | varchar(20) start_at | timestamp
Sample results
video_category | user_id -------------------+--------- Beauty & Skincare | 8000001 Beauty & Skincare | 8000003 Beauty & Skincare | 8000008 Dance | 8000001 Dance | 8000002
Solution postgres
SELECT video_category, user_id
FROM video_session
WHERE DATE(start_at) = '2021-08-01'
GROUP BY user_id, video_category
HAVING COUNT(video_id) >= 2
ORDER BY video_category;
Explanation
This query is selecting data from a table called "video_session". It is specifically selecting the "video_category" and "user_id" columns. The query is filtering the data and only selecting records where the date in the "start_at" column is equal to August 1, 2021.
The query then groups the data by both "user_id" and "video_category". It then filters the grouped records by only selecting records where the count of "video_id" is greater than or equal to 2. This means that only records where a user has watched at least 2 videos in a specific category on August 1, 2021 will be returned.
Finally, the query orders the results by the "video_category" column in ascending order.
Expected results