American Express SQL Interview Question and Solution | Page Recommendation

[@addhyasumitra90]

On the occasion of Guru Purnima, i would like to bow down to you Ankit sir. thank you for all the easy-to-digest explanations and problems, became a SQL fan of yours since last 1 week 🙏

[@ankitbansal6]

Thank you so much 🙏 It means a lot to me 💝

[@rahulmittal116]

I felt myself as sql expert but not able to solve this question. Need to work more on sql now. Thanks Ankit for sharing such question where we can test our SQL level of expertise.

[@mantisbrains]

select f.user_id,fp.page_id from friends f inner join likes fp on f.friend_id = fp.user_id where concat(f.user_id,fp.page_id) not in (select concat(f.user_id,fp.page_id) as users_likes from friends f inner join likes fp on f.user_id = fp.user_id) Thanks Ankit, really simplified complex problem for me !

[@Chathur732]

SHORT SOLUTION: (MYSQL) with cte as ( select distinct f.user_id, l.page_id from friends f join likes l on f.friend_id = l.user_id ) select user_id, page_id from cte where (user_id, page_id) not in (select user_id, page_id from likes) Thanks, Ankit Sir! I have successfully attempted all your 70 problems. It's been challenging and a great learning experience for me. I'm looking forward to solving many more SQL problems with you. ☺

[@rainbowkids0606]

thanks ankit for this question.....the question looked simple while reading but implementing is quiet confusing..... your second solution made it very easy to understand

[@myjourney352]

Thank you so much Ankit, I did it in postgres. with cte as ( select f.user_id, l.page_id from likes l join friends f on f.friend_id = l.user_id) select user_id,page_id from cte except (select * from likes) order by 1,2

[@Cherupakstmt]

Best solution 🎉

[@sahibathakral]

Solution 3 is by far the best one

[@ratankj4402]

This question is really good. thanks for sharing this question.

[@MeanLifeStudies]

Thank you for supporting me and for great contributions to the community, make me realise to contribute something to the community. So I started recently contributing to the community with SQL interview preparation, problem and solution series. Soon I will contribute many things like you. Thank you.

[@kadagaladurgesh3691]

I am also following your interview series on sql your doing great job 👍👍

[@suyashgupta6169]

For the first time in 71 videoes of this playlist , i think i got better or easy to understand solution as compared to yours! select f.user_id,l.page_id from friends f JOIN likes l on f.friend_id = l.user_id except select user_id,page_id from likes order by user_id,page_id

[@DEwithDhairy]

Solution 3 clicked in my mind !😀 with cte as ( select friend_id , page_id , concat(friend_id,page_id) as fp from friends as t1 inner join likes as t2 on t1.user_id = t2.User_id ) , cte1 as ( select * from cte as t1 where fp not in (select concat(user_id ,page_id) as up from likes) ) select friend_id , page_id from cte1 group by friend_id , page_id order by friend_id;

[@uttamx2016]

Thank you so much for real life problems I bit unclear on 2nd solution but 3rd solution and tested in system .

[@Quenchedfooty]

select DISTINCT a.user_id, b.page_id from friends a inner join likes b on a.friend_id = b.user_id EXCEPT select * from likes

[@Datapassenger_prashant]

I don't know why, but this seems to be a toughest question for me from your channel when tried to solve without watching your solution..watched it multiple times afterwards.

[@ankitbansal6]

There are some simpler solutions in the comment section

[@Datapassenger_prashant]

@@ankitbansal6 no sir, please don't feel there is any thing wrong in the explanation and I am sorry If I sounded like that.. i was just sharing that I found it difficult to solve this of my own. your solutions and explanations are always top notch.

[@ankitbansal6]

​@@Datapassenger_prashant no no .just for your information I told you 😅

[@RightLearningPath]

This would also work, this is using "except" operator, -- Pages liked by their friends select f.User_Id,L.Page_Id from friends f join Likes l on f.Friend_Id =l.User_Id except -- Pages liked by the person select user_id, Page_id from Likes

[@pritampriyadarshan720]

Well done buddy, this one is quick

[@akashgoel601]

thank you, posting my query: with cte_l as ( select distinct f.user_id,f.friend_id,l.page_id from friends f join likes l on f.user_id=l.user_id --where f.user_id=1 ),cte_l2 as ( select friend_id,page_id from cte_l ) select * from cte_l2 EXCEPT select * from likes

[@dibyadarshan7187]

just wanted to know whats the experience range for these kind of questions ? I mean whats the experience of the candidate interviewed

[@sahilummat8555]

Hey Ankit Sir My solution for the same ;with liked_by_user as ( select f.*,l1.page_id as page_liked_by_user from friends f join likes l1 on f.user_id=l1.user_id ),liked_by_friend as ( select f.*,l.page_id as page_liked_by_friend from friends f join likes l on f.friend_id=l.user_id) select distinct user_id,page_liked_by_friend from liked_by_friend except select distinct user_id,page_liked_by_user from liked_by_user order by user_id

[@gameply347]

Sir can you please make a video on Cursor.

[@Ankitatewary-q6w]

Logic applied- Found out which pages liked by friend and then excluded those pages which were liked by a particular user already. with cte as( select distinct f.user_id, l.page_id as friend_like from friends f join likes l on f.friend_id=l.user_id) select user_id,friend_like as recommended_page from cte where friend_like not in (select distinct page_id from likes where likes.user_id=cte.user_id);

[@vaibhavverma1340]

My Solution using Except Operator and Happy Guru Purnima Ankit sir :) with cte as (select distinct f.user_id, l.page_id from friends f join likes l on f.friend_id = l.user_id) select user_id, page_id from cte except select user_id, page_id from likes

[@Datapassenger_prashant]

this was the shortest , thanks for your effort and sharing the solution

[@NavneetKaur-08]

This is actually simpler. Thanks for sharing!

[@devrajpatidar5927]

Thank you for such a nice problem here is my approach :- (select distinct f.user_id,l.page_id from friends_rec as f inner join likes_fr as l on f.friend_id = l.user_id) except ( select distinct f.user_id,l.page_id from friends_rec as f inner join likes_fr as l on f.user_id = l.user_id );

[@nijamuddinshaik9130]

in oracle select a.user_id,c.page_id from friends a,likes c where a.friend_id=c.user_id(+) minus select a.user_id,b.page_id from friends a,likes b where a.user_id=b.user_id (+) ;

[@ankitdhar3692]

with cte as( --liked by friends select distinct f.user_id,l.page_id from friends as f join likes as l on f.friend_id=l.user_id ), cte2 as( --liked by user select distinct l.user_id, l.page_id from friends as f join likes as l on f.user_id=l.user_id ) select * from cte as a where page_id not in ( select page_id from cte2 where a.user_id=user_id) Used the concept of correlated subquery.

[@sumitsaraswat5884]

Thanks Ankit bro it was amazing explanation, Could you tell me how can I submit questions?

[@ankitbansal6]

Send it to [e-mail]

[@akashkothawade2939]

My Solution: (select a.user_id,b.page_id from friends a inner join likes b on a.friend_id = b.user_id group by 1,2 order by 1) -- This query will give the pages liked by friends for each user minus (select user_id,page_id from likes order by user_id); -- After minus we will get pages which are not liked by user but by his friends

[@ritudahiya6223]

@ankitbansal . I bought your python course on 4th July and after your new website I am unable to login . It says invalid email id..please resolve the issue asap as I was studying python for an interview

[@ritudahiya6223]

@ankit bansal

[@ritudahiya6223]

@ankit bansal

[@ankitbansal6]

Responded on email

[@ritudahiya6223]

@@ankitbansal6 issue resolved.thank you

[@gowtham9450]

Can u tel me any online platform to write and execute query for practice

[@ankitbansal6]

It will be available on namaste SQL this week

[@gowtham9450]

@@ankitbansal6 that's fine but am asking for any online sql editor or else please advice how to avail and install Oracle express edition

[@apppu1k221]

@@gowtham9450 you can download MS SQL desktop version for practise

[@deepakulkarni4052]

How to switch from support to SQL developer is it possible?

[@varunas9784]

Thanks for the video, great one as always! Below is my solution on MS SQL server: =============================================== with cte as (select l.user_id, l2.page_id from likes l join friends f on l.user_id = f.friend_id join likes l2 on l2.user_id = f.user_id and l.page_id l2.page_id ) select distinct USER_ID, page_id from cte EXCEPT Select * from likes ===============================================

[@Sai-dc7lc]

@Ankit, It’s been a week since the sql video recordings are not opening. Even when I tried reloading the video, it’s not showing anything. Could you please look into it?

[@ankitbansal6]

Please send an email to [e-mail]

[@InspireWithShubh]

❤

[@pulcheguj]

Hey ankit 4, 5 months backl i purchased your course it was life time access but now it is showing in my courses but again it is telling to buy these courses.

[@ankitbansal6]

No you just have to click on take course

[@pulcheguj]

@@ankitbansal6 yes its working...... thank you so much...actually i have completed the sql course but my python i was not able to complete at that time because of work pressure....but this sql one helped me alot....i have secured 12 lpa from this

[@ankitbansal6]

@@pulcheguj awesome 👍

[@himanshuparihar9888]

select distinct a.user_id , b.page_id from friend_s as a join like_s as b on a.friend_id = b.user_id and ( a.user_id , b.page_id) not in (select * from like_s);

[@HongKing-u4y]

Please put your courses on udemy

[@mohdtoufique7446]

Hi Ankit! Thanks for the content My approach WITH friend_liked_pages AS( SELECT DISTINCT a.user_id,b.page_id AS friend_liked_pages FROM friends a INNER JOIN LIKES b ON a.friend_id=b.user_id), user_liked_pages AS( SELECT DISTINCT a.user_id,b.page_id AS user_liked_pages FROM friends a LEFT JOIN LIKES b ON a.user_id=b.user_id) SELECT c.user_id,friend_liked_pages FROM friend_liked_pages c LEFT JOIN user_liked_pages d ON c.user_id=d.user_id AND friend_liked_pages=user_liked_pages WHERE user_liked_pages IS NULL

[@galaxyalias9526]

Hi Ankit, I believe the idea is simply to get the pages recommendation for each user and then subtract the already existing liked pages. Implementing that logic, here is a much easier solution: SELECT friend_id, page_id FROM likes l INNER JOIN friends f ON f.user_id=l.user_id WHERE (friend_id,page_id) NOT IN (SELECT user_id, page_id FROM likes) GROUP BY friend_id, page_id

[@moushumibose4290]

select distinct f.user_id,page_id as recommended_page from friends f left join likes l on f.friend_id = l.user_id except select user_id,page_id as recommended_page from likes

[@Damon-007]

--My solution ( Ms SQL) select distinct f.user_id as f_id,l.page_id as recommend_page from friends f inner join likes l on f.friend_id=l.user_id left join likes l2 on l.page_id=l2.page_id and f.user_id=l2.user_id where l2.page_id is null;

[@arpanscreations6954]

My short solution: select distinct f.user_id, lf.page_id from friends f inner join likes lf on f.friend_id = lf.user_id left join likes l on f.user_id = l.user_id and lf.page_id=l.page_id where l.user_id is null order by f.user_id

[@shivamchoudhury5421]

Hi @ankit, I have pasted my code. select distinct a.friend_id as user_id, page_id from friends a left join likes b on a.user_id = b.user_id where concat(friend_id, page_id) not in (select concat(user_id, page_id) concat_ids from likes);

[@DEwithDhairy]

Pyspark version of this problem : kzbin.info/www/bejne/bn6viZZrZtmqjq8si=KP0soQrXTX7hYPsf

[@ank_kumar12]

with cte as (select f1.user_id,l1.page_id from friends f1 join likes l1 on f1.friend_id=l1.user_id group by f1.user_id,l1.page_id ) select * from cte where (user_id,page_id) not in (select user_id,page_id from likes)

[@lovishbabar2154]

Hello! this Questions is very confusing

[@AmanRaj-p8w]

Mysql solution: with cte as ( select f.user_id as user_id1, friend_id,l.* from friends as f inner join likes as l on f.friend_id = l.user_id ) ,cte2 as ( select user_id1 , c.friend_id, l.page_id, c.page_id as pge_id from cte as c left join likes as l on c.user_id1 = l.user_id and c.page_id = l.page_id where l.page_id is null ) select distinct user_id1, friend_id, group_concat(pge_id) as page_id from cte2 group by user_id1, friend_id

[@boppanakishankanna6029]

My solution in ms sql server- SELECT DISTINCT f.user_id,page_id as pages_to_be_recommended FROM friends f INNER JOIN likes l ON f.friend_id=l.user_id EXCEPT SELECT * FROM likes;

[@adityakishan1]

with cte1 as ( select f.user_id, f.friend_id, l.page_id from friends f inner join likes l on f.friend_id = l.user_id ) select distinct user_id, page_id from cte1 where concat(user_id,page_id) not in (select concat(user_id,page_id) from likes) order by user_id

[@shreyaprasad5203]

with cte as ( select u.userid, u.friendid,p.pageid as page_f_liked from userid u inner join page p on u.friendid = p.userid), cte2 as ( select c.*,p2.pageid as pages_user_liked from cte c left join page p2 on c.user_id = p2.user_id and c.page_f_liked = p2.pageid) select userid,pages_f_liked as recommended_pages from cte2 where pages_user_liked is null

[@akshobshekhar736]

with cte as (select f.*,l.page_id as friend_liked from friends f join likes l on friend_id = l.user_id) select distinct cte.user_id,friend_liked as page_sugg from cte left join likes on friend_liked =page_id and cte.user_id = likes.user_id where page_id is null order by 1

[@FromPlanetZX]

Select distinct A.user_id, B.page_id as recommended_page_id from friends A JOIN likes B ON A.friend_id = B.user_id EXCEPT Select * from likes;

[@darshakkumarranpariya867]

with cte as ( select friends.*, likes.page_id from friends inner join likes on friends.friend_id = likes.user_id ) select distinct user_id, page_id from cte where page_id not in (select page_id from likes where user_id=cte.user_id);

[@SharanGhatge]

In PostgreSQL select f.user_id, page_id from friends f join likes l ON f.friend_id = l.user_id EXCEPT select f.user_id, page_id from friends f join likes l ON f.user_id = l.user_id

[@gulammoin5956]

We can also do without concat. #### Method - 4 select f.user_id , fp.page_id from friends f inner join likes fp on f.friend_id=fp.user_id where (f.user_id,fp.page_id) NOT IN ( select f.user_id , up.page_id from friends f inner join likes up on f.user_id=up.user_id )

[@vinuvicky1560]

POSTGRESQL SOLUTION: select Distinct f.user_id, l.page_id from friends f join likes l on f.friend_id=l.user_id where (f.user_id,l.page_id) not in (select user_id, page_id from likes) order by 1

[@saurabhpandey5296]

with cte as (select t1.user_id, t1.friend_id, t2.page_id as us_pg, t3.page_id as fr_pg from friends t1 left join likes t2 on t1.user_id = t2.user_id left join likes t3 on t1.friend_id = t3.user_id ) select distinct user_id, fr_pg from cte t1 where fr_pg not in (select distinct us_pg from cte t2 where t2.user_id = t1.user_id)

[@aayushigupta6680]

My Approach for this using the 'EXCEPT' keyword: -- query gives all combinations where the friend hs liked the page SELECT f.user_id, l.page_id FROM friends f JOIN likes l ON f.friend_id = l.user_id EXCEPT -- query gives all combinations where the user_id has liked a page SELECT f.user_id, l1.page_id FROM friends f JOIN likes l1 ON f.user_id = l1.user_id

[@DodlaPankaj]

with a as ( SELECT f.user_id, l.page_id FROM friends f JOIN likes l on f.friend_id = l.user_id ) select distinct * from a where (user_id,page_id) not in (select * from likes)

[@PraveenSinghRathore-df3td]

with cte as (select f.*,l.page_id as fp_id from likes l join friends f on l.user_id = f.friend_id order by f.user_id), cte2 as (select cte.*, dp.page_id from likes dp right join cte on cte.fp_id = dp.page_id and cte.user_id = dp.user_id) select distinct user_id,fp_id as page_to_be_liked from cte2 where page_id is null order by 1,2;

[@vighneshgaikwad575]

Idea Inclusion Exclusion : Include all the potential pages to give to the each_user (L JOIN friends.friend_Id on likes.user_id) Exclude those which have been liked by the user itself (L JOIN the above table on friends.user_id & likes.page_id if the page is only liked by user's friend then page if here will be NULL else present) SELECT DISTINCT u.user_id, l.page_id FROM friends u LEFT JOIN likes l ON l.user_id = u.friend_id LEFT JOIN likes ul ON ul.user_id = u.user_id AND ul.page_id = l.page_id WHERE ul.page_id is NULL

[@vibhavbhat2011]

Please find my solution with cte as (select f.* , l.page_id from friends as f inner join likes as l on f.friend_id = l.user_id ) select distinct user_id , page_id from cte as c where page_id not in (select page_id from likes where user_id = c.user_id)

[@arjundev4908]

Without using the table Friends. Just with Likes Table WITH CTE AS(SELECT U.*,p.* FROM(SELECT DISTINCT user_id FROM LIKES) AS U CROSS JOIN (SELECT DISTINCT page_id FROM LIKES) AS p) SELECT C.* FROM CTE AS C LEFT JOIN LIKES AS L ON C.user_id = L.user_id AND C.page_id = L.page_id WHERE L.user_id IS NULL ORDER BY 1;

[@reachrishav]

I tried this. WITH likes_by_friends AS ( SELECT f.user_id, l.page_id FROM friends f JOIN likes l on f.friend_id = l.user_id ) SELECT user_id, page_id FROM likes_by_friends EXCEPT SELECT user_id, page_id FROM likes

[@sudhanshudeshmukh]

Simple Solution SELECT USER_ID,PAGE_ID FROM (SELECT F.USER_ID,F.FRIEND_ID,L.PAGE_ID FROM FRIENDS F,LIKES L WHERE F.FRIEND_ID=L.USER_ID) MINUS SELECT USER_ID,PAGE_ID FROM LIKES;

[@Riteshkumar-r5o]

POSTGRE SQL SOLUTION: (SELECT f.user_id,l.page_id FROM friends f INNER JOIN likes l ON f.friend_id=l.user_id) EXCEPT (SELECT f.user_id,l.page_id FROM friends f JOIN likes l ON f.user_id=l.user_id)

[@exanode]

My oracle solution: WITH friend_likes AS ( SELECT f.user_id, f.friend_id, l.page_id FROM friends f LEFT JOIN likes l ON f.friend_id = l.user_id ) SELECT DISTINCT fl.user_id, fl.page_id FROM friend_likes fl WHERE fl.page_id NOT IN ( SELECT l.page_id FROM likes l WHERE l.user_id = fl.user_id ) ORDER BY fl.user_id, fl.page_id;

[@nd9267]

select distinct l.user_id, li.page_id from likes l join friends f on l.user_id = f.friend_id join likes li on f.user_id = li.user_id where l.page_id li.page_id and li.page_id not in (select page_id from likes lk where lk.user_id = l.user_id) order by l.user_id

[@mohammaddanishkhan7288]

Let me know if this solution is more easy and correct, I got the same output as you sir: WITH friend_liked_pages AS ( SELECT f.user_id, l.page_id FROM friends f JOIN likes l ON f.friend_id = l.user_id --ORDER BY f.user_id ) SELECT * FROM friend_liked_pages EXCEPT SELECT * FROM likes;

[@radhikamaheshwari4835]

with cte as ( select f.user_id as f_user_id, f.friend_id as f_friend_id, page_id from friends f join likes l on f.friend_id = l.user_id ) select c.f_user_id, c.page_id from cte c join likes l on c.f_user_id = l.user_id where c.page_id not in (select page_id from likes where user_id = c.f_user_id) group by c.f_user_id, c.page_id order by f_user_id

[@muchkundtiwari5561]

My sol with cte as( Select friend.user_id,likes.page_id From friends Inner join likes on friends.user_id = likes.user_id group by friends.user_id,likes.page_id order by friends.user_id,likes.pages_id), Cte2 as ( Select friend.user_id,likes.page_id From friends Inner join likes on friends.friend_id = likes.user_id group by friends.user_id,likes.page_id order by friends.user_id,likes.pages_id) where (Cte2.user_id,cte.page_id) not in (select cte.user_id,cte.page_id from cte) Order by user_id,page_id;

[@VedanshiReddy131]

SELECT DISTINCT user_id ,page_id FROM ( SELECT f.user_id,f.friend_id,l.page_id FROM #friends f JOIN #likes l ON f.friend_id = l.user_id EXCEPT SELECT f.user_id,f.friend_id,l.page_id FROM #friends f JOIN #likes l ON f.user_id = l.user_id ) AS A ORDER BY 1

[@ashishkumarjha2587]

-- IF using MYSQL workbench WITH cte AS (SELECT f.*,l.page_id FROM friends f JOIN likes l ON f.friend_id = l.user_id) SELECT user_id , page_id FROM cte c WHERE c.page_id NOT in (SELECT page_id FROM likes WHERE user_id = c.user_id) GROUP BY user_id , page_id ORDER BY user_id , page_id;

[@ethyria7685]

with cte as (SELECT a.user_id, friend_id, page_id FROM friends a LEFT JOIN likes b ON a.friend_id = b.user_id) SELECT distinct a.user_id, a.page_id FROM cte a LEFT JOIN likes b on a.user_id = b.user_id AND a.page_id = b.page_id WHERE b.user_id IS NULL

[@rajatsrivastava2792]

with users_page as( select distinct f.user_id , l.page_id from friends f inner join likes l on f.user_id = l.user_id ) friend_pages as ( select distinct f.user_id ,f.friend_id, l.page_id from friends f inner join likes l on f.friend_id = l.user_id ) select from friend_pages c left join users_page up on c.user_id = up.user_id and c.page_id = up.page_id

[@SanjayKumar-rw2gj]

with friends_like as( SELECT f.user_id,f.friend_id,l.page_id from friends f inner join likes l on f.friend_id=l.user_id ) SELECT DISTINCT fl.user_id,fl.page_id from friends_like fl LEFT JOIN likes l ON fl.user_id=l.user_id AND fl.page_id=l.page_id WHERE l.user_id is null order by 1

[@atharwaborkar6778]

with cte1 as ( select b.user_id , b.friend_id , l.page_id from friends b join likes l on b.user_id != l.user_id group by b.user_id, l.page_id order by b.user_id ),cte2 as ( select b.user_id , b.friend_id , l.page_id from friends b join likes l on b.user_id = l.user_id group by b.user_id, l.page_id order by b.user_id ) select c1.user_id , c1.page_id from cte1 c1 left join cte2 c2 on c1.user_id = c2.user_id and c1.page_id = c2.page_id where c2.user_id is NULL order by c1.user_id;