Answer to Question #185027 in Databases | SQL | Oracle | MS Access for bhadra

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_idno_of_likes

sir plz explain the sql query

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