Databases | SQL | Oracle | MS Access Answers

Get top 5 genres in the platform with most positive user activity, i.e., the genres with videos having more number of likes.

Note:

• Consider the reaction_type LIKE as liked.
• If a video belongs to 3 genres, then the likes of the video is counted in all the 3 genres.
• Sort the output in the descending order of no_of_likes, and then in the ascending order of the genre_type.

Expected Output Format

genre_id genre_type no_of_likes

.... ... ..

Get all the potential users. Fetch the user_ids who liked at least 2 videos published by "Disney" channel, and who did not subscribe to the channel (channel_id = 352).

Note:

• Consider reaction_type LIKE as liked.
• Sort the output in the descending order of no_of_likes, and then in the ascending order of potential_user_id.

Expected Output Format

potential_user_id no_of_likes

... ...

Get all the user_ids who liked at least 5 videos published by "Tedx" channel.

Note:

• Consider reaction_type LIKE as liked.
• Sort the output in the descending order of no_of_likes, and then in the ascending order of active_user_id.

Expected Output Format

active_user_id no_of_likes

... ...

Get the active users of the platform. Consider the users who liked at least fifty videos are considered active users.

Note:

• Consider reaction_type LIKE as liked.
• Sort the output in the ascending order of user_id.

Expected Output Format

active_user_id no_of_likes

... ...

Best time to upload a comedy video:

DunkFest channel is planning to upload a video in the "COMEDY" genre

Give the channel the best suitable hour of the day when users positively engage more with comedy videos.

Note:

• Consider positive engagement as LIKE for the videos in the "COMEDY" genre_type.
• Consider reaction_type LIKE as liked.
• Return the hour in the integer format

Expected Output Format

hour_of_engagement no_of_likes

5 ...

Get the top 5 viewed videos that belong to the "GAMING" genre_type.

Note:

• Sort the output in the descending order of no_of_views, and then in the ascending order of video_name.

Expected Output Format

video_name no_of_views

Get to top 5 viewed videos which belong to both the genres "Action" (genre_id = 201 ) and "Comedy" (genre_id = 202).

Note:

• In case, if the no_of_views are same, then sort the output in the ascending order of video_name.

Expected Output Format

video_name no_of_views

Get all the distinct user_ids who liked at least one video uploaded by Android Authority Channel (channel__id = 364) but didn't like the video uploaded by Tech savvy channel with video_id = 1005.

Note:

• Consider reaction_type LIKE as liked
• Sort the output in the ascending order of user_id.

Expected Output Format

user_id

Get all the videos that have more than the average number of views.

Note:

• Sort the output in the ascending order of name.

Expected Output Format

name no_of_views

How many times each user has engaged with the videos of "News for you" channel (id = 366).

Note:

• Consider engagement as LIKE or DISLIKE for a video uploaded by News for you channel.
• Ignore the users who did not engage with the channel at least once.
• Sort the output in the descending order of no_of_reactions, and then in the ascending order of user_id.

Expected Output Format

user_id no_of_reactions......