WITH user_group as ( select greatest(message_sender_id, message_receiver_id) as user_1, least(message_sender_id, message_receiver_id) as user_2, count(*) from whatsapp_messages group by greatest(message_sender_id, message_receiver_id), least(message_sender_id, message_receiver_id) ) select count (distinct concat(user_1,user_2)) from user_group group by ();
I couldn't solve it two years ago for my meta interview (campus hire, final round). Today when I was working on a similar query and I solved it within 5 minutes. The query took me back to 2 years ago and now I found your solution exactly matched mine. How time flies.
If I sound a bit rough in this video, it's because I had a slight cold. I really wanted to stay consistent on the channel so I still decided to record some videos.
Wow just found your channel through a LinkedIn post where one got selected in company. I will also be doing questions in your videos thanks for creating content. ❤️
CASE WHEN Solution: select count(distinct case when message_sender_id < message_receiver_id then concat(message_sender_id, message_receiver_id) else concat(message_receiver_id , message_sender_id) end) from whatsapp_messages;
UNION Solution: SELECT COUNT(*) FROM (SELECT message_sender_id, message_receiver_id FROM whatsapp_messages UNION SELECT message_receiver_id, message_sender_id FROM whatsapp_messages) chats WHERE message_sender_id < message_receiver_id
thats great fredrick below goes my soln Please review With cte as (Select *from msgrble a join magrbl b on a. Sender_id=b.reciever_id and b. Sender_id=a.reciever_id) Select ((Select count(slender_id) as cnt from cte )) +select count(sender_id) from (select sender_id from msgtble where sender_id not in (select sender_id from cte)) c
The first error that comes up when I try to run this is: syntax error at or near "select" LINE 2: ...ct ((Select count(slender_id) as cnt from cte )) +select cou... You can't use arithmetic operators such as + between SELECT statements. After all, there's no guarantee that the output of each SELECT statement will be a single value. SQL checks this condition before it runs which is why it throws an error here. Also, I don't think the JOIN idea here works here (though it is creative). If you join on the condition in your CTE, you'll basically filter the table to messages that have a counterpart (where the receiver also sends a message to sender).
Don’t worry, if you solved one of these you’ll be able to solve all of the similar ones that have that bidirectional relationship of friends/conversations/etc. You just gotta recognize the pattern.
Try this question yourself: platform.stratascratch.com/coding/2086-number-of-conversations?via=frederik
WITH user_group as
(
select greatest(message_sender_id, message_receiver_id) as user_1,
least(message_sender_id, message_receiver_id) as user_2,
count(*)
from whatsapp_messages
group by greatest(message_sender_id, message_receiver_id),
least(message_sender_id, message_receiver_id)
)
select count (distinct concat(user_1,user_2))
from user_group
group by ();
I couldn't solve it two years ago for my meta interview (campus hire, final round). Today when I was working on a similar query and I solved it within 5 minutes. The query took me back to 2 years ago and now I found your solution exactly matched mine. How time flies.
Thanks for sharing your story! You definitely made a lot of progress.
If I sound a bit rough in this video, it's because I had a slight cold. I really wanted to stay consistent on the channel so I still decided to record some videos.
so grateful for you
Wow just found your channel through a LinkedIn post where one got selected in company. I will also be doing questions in your videos thanks for creating content. ❤️
Thanks
CASE WHEN Solution:
select
count(distinct case when message_sender_id < message_receiver_id then
concat(message_sender_id, message_receiver_id) else concat(message_receiver_id , message_sender_id) end)
from whatsapp_messages;
Pls increase font size of the query. Great work BTW.
Thank you for the feedback, I’ll try to zoom in from now on.
UNION Solution:
SELECT COUNT(*) FROM
(SELECT message_sender_id, message_receiver_id FROM whatsapp_messages
UNION
SELECT message_receiver_id, message_sender_id FROM whatsapp_messages) chats
WHERE message_sender_id < message_receiver_id
What if instead of "WHERE message_sender_id < message_receiver_id" you simply do "COUNT(*)/2"?
thats great fredrick below goes my soln
Please review
With cte as (Select *from msgrble a join magrbl b on a. Sender_id=b.reciever_id and b. Sender_id=a.reciever_id)
Select ((Select count(slender_id) as cnt from cte )) +select count(sender_id) from (select sender_id from msgtble where sender_id not in (select sender_id from cte)) c
The first error that comes up when I try to run this is:
syntax error at or near "select"
LINE 2: ...ct ((Select count(slender_id) as cnt from cte )) +select cou...
You can't use arithmetic operators such as + between SELECT statements. After all, there's no guarantee that the output of each SELECT statement will be a single value. SQL checks this condition before it runs which is why it throws an error here.
Also, I don't think the JOIN idea here works here (though it is creative). If you join on the condition in your CTE, you'll basically filter the table to messages that have a counterpart (where the receiver also sends a message to sender).
This one was really difficult.Couldn't figure it out.
Don’t worry, if you solved one of these you’ll be able to solve all of the similar ones that have that bidirectional relationship of friends/conversations/etc.
You just gotta recognize the pattern.