Answer to Question #284162 in Databases | SQL | Oracle | MS Access for Lovely

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