Get Watched Videos By Your Friends - Leetcode Solution

Problem:

You are given a table of watched videos by your friends. Each entry is represented as a tuple of (friend_id, video_id) where friend_id is the id of the friend who watched the video and video_id is the id of the video that was watched.

Your task is to write a SQL query that finds all the distinct watched videos by your friends and order them by their frequency in descending order.

The output table should have two columns:

video_id - the id of the video watch_count - the number of times the video was watched The output table should be ordered by the watch_count in descending order.

Solution:

To solve this problem, we need to join two tables - the friend watch table and the video table. We need to count the number of times a video was watched and order the result in descending order based on the watch_count.

First, let's create the tables and insert some sample data:

CREATE TABLE friend_watch ( friend_id INT, video_id INT );

CREATE TABLE video ( id INT, name VARCHAR(50) );

INSERT INTO friend_watch VALUES (1, 1); INSERT INTO friend_watch VALUES (1, 2); INSERT INTO friend_watch VALUES (2, 1); INSERT INTO friend_watch VALUES (2, 3); INSERT INTO friend_watch VALUES (3, 1); INSERT INTO friend_watch VALUES (3, 2); INSERT INTO friend_watch VALUES (3, 4);

INSERT INTO video VALUES (1, 'video1'); INSERT INTO video VALUES (2, 'video2'); INSERT INTO video VALUES (3, 'video3'); INSERT INTO video VALUES (4, 'video4');

Now, let's write the SQL query to solve the problem:

SELECT fw.video_id, COUNT(*) AS watch_count FROM friend_watch fw JOIN video v ON fw.video_id = v.id GROUP BY fw.video_id ORDER BY watch_count DESC;

In this query, we are joining the friend_watch table with the video table on the video_id column. We are then grouping the result by video_id and counting the number of times a video was watched. We are finally ordering the result in descending order based on the watch_count column.

The output of the query is:

+----------+-------------+ | video_id | watch_count | +----------+-------------+ | 1 | 3 | | 2 | 2 | | 3 | 1 | | 4 | 1 | +----------+-------------+

In this output, we can see that video1 was watched 3 times, video2 was watched 2 times, and video3 and video4 were each watched once. This is the correct solution to the problem.

1