Answer to Question #241296 in Databases | SQL | Oracle | MS Access for raj

Question

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

table:

View Schema

user_likes

user_id video_id reaction_type reacted_at

video

channel_id

channel

channel_id name owner_id

Answer 1

SELECT u.`id`, u.`first_name` AS `firstName`, u.`last_name` AS `lastName`, u.`email`,
       us.`id` AS `usersSecondaryEmails.id`,
       us.`email` AS `usersSecondaryEmails.email`
FROM `Users` u LEFT OUTER JOIN
     `UsersSecondaryEmails` us
     ON u.`id` = us.`user_id`
WHERE (u.`first_name` LIKE '%bob%' OR u.`last_name` LIKE '%bob%' OR u.`email` LIKE '%bob%') OR
      EXISTS (SELECT 1
              FROM `UsersSecondaryEmails` us2
              WHERE us2.user_id = us.user_id AND us2.`email` LIKE '%bob%'
             );