Answer to Question #234671 in Databases | SQL | Oracle | MS Access for Suraj

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

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%'