SQL INTERVIEW QUESTION | Friend Request suggestions in META

[@ritammoharana4219]

Another Approach Using Postgres---
WITH cte AS
(SELECT
c.user_id,c1.user_id AS usr
,ROW_NUMBER() OVER(PARTITION BY c.user_id,c1.user_id) AS rn
FROM transactions AS c
JOIN
transactions AS c1 ON c.day = c1.day and c.post_id = c1.post_id and c.user_id != c1.user_id
order by c.user_id,c1.user_id),
cte1 AS
(SELECT user_id,usr
FROM cte WHERE rn = 3)
SELECT user_id,usr AS user_id FROM cte1
EXCEPT
(SELECT user1_id,user2_id FROM friend
union
SELECT user2_id,user1_id FROM friend) order by 2;

[@hanumanthprasad6787]

Sir last step not understanding from group by and having count can you please elaborate group by after getting the result then add count seperately then we can understand if possible please give the clarity on last step its humble request

[@datasculptor2895]

We are doing group by and using having to filter out rows whose count is less than 3

[@subba18]

WITH T1 AS
(select USER_ID,post_id||day as post, COUNT() OVER(PARTITION BY USER_ID) AS COUNT from transactions )
SELECT A.USER_ID, B.USER_ID FROM T1 A, T1 B WHERE A.POST = B.POST AND A.USER_ID != B.USER_ID AND A.COUNT = B.COUNT
AND ((A.USER_ID, B.USER_ID ) NOT IN ( SELECT * FROM friend)
AND (B.USER_ID, A.USER_ID ) NOT IN ( SELECT * FROM friend))
GROUP BY A.USER_ID, B.USER_ID
HAVING COUNT(*) =A.COUNT