American Express SQL Interview Question and Solution | Page Recommendation
Vložit
- čas přidán 7. 09. 2024
- In this video we will discuss a SQL interview question asked in American Express Data Analyst Interview.
High Quality Analytics Courses : www.namastesql...
script:
CREATE TABLE friends (
user_id INT,
friend_id INT
);
-- Insert data into friends table
INSERT INTO friends VALUES
(1, 2),
(1, 3),
(1, 4),
(2, 1),
(3, 1),
(3, 4),
(4, 1),
(4, 3);
-- Create likes table
CREATE TABLE likes (
user_id INT,
page_id CHAR(1)
);
-- Insert data into likes table
INSERT INTO likes VALUES
(1, 'A'),
(1, 'B'),
(1, 'C'),
(2, 'A'),
(3, 'B'),
(3, 'C'),
(4, 'B');
Zero to hero(Advance) SQL Aggregation:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #dataengineer
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. ☺
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.
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 🙏
Thank you so much 🙏 It means a lot to me 💝
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
Best solution 🎉
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.
I am also following your interview series on sql your doing great job 👍👍
This question is really good. thanks for sharing this question.
Solution 3 is by far the best one
Thank you so much for real life problems
I bit unclear on 2nd solution but 3rd solution and tested in system .
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
this was the shortest , thanks for your effort and sharing the solution
This is actually simpler. Thanks for sharing!
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
);
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
Well done buddy, this one is quick
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
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;
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);
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
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.
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
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.
There are some simpler solutions in the comment section
@@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.
@@Datapassenger_prashant no no .just for your information I told you 😅
just wanted to know whats the experience range for these kind of questions ? I mean whats the experience of the candidate interviewed
Thanks Ankit bro it was amazing explanation, Could you tell me how can I submit questions?
Send it to sql.namaste@gmail.com
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);
Sir can you please make a video on Cursor.
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)
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
❤
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
===============================================
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
@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
@ankit bansal
@ankit bansal
Responded on email
@@ankitbansal6 issue resolved.thank you
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
@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?
Please send an email to sql.namaste@gmail.com
How to switch from support to SQL developer is it possible?
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
Please put your courses on udemy
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.
No you just have to click on take course
@@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
@@pchoijhldghnct56 awesome 👍
--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;
Can u tel me any online platform to write and execute query for practice
It will be available on namaste SQL this week
@@ankitbansal6 that's fine but am asking for any online sql editor or else please advice how to avail and install Oracle express edition
@@gowtham9450 you can download MS SQL desktop version for practise
Hello!
this Questions is very confusing
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
Pyspark version of this problem :
czcams.com/video/8MLXc51twXM/video.htmlsi=KP0soQrXTX7hYPsf
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;
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);
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
)
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);
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
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)
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
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)
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)
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
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;
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
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;
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
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;
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
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)
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
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
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;