CTE necessary or multiple joins

Question

I have a table that is called friends as below:

column          type
user_id        integer
friend_user_id integer

tabled called page likes as below:

page_likes

column        type
user_id       integer
page_id       integer

I want to output the fields below: Basically show the user_id, page_id and the # of friends that like that page, but not the pages that the actual user already likes.

user_id, page_id, count(distinct friend_user_id)

WITH t1 AS (
    SELECT 
        f.user_id 
        , f.friend_user_id
        , pl.page_id 
    FROM friends AS f
    INNER JOIN page_likes AS pl
        ON f.friend_user_id = pl.user_id
)

SELECT t1.user_id, t1.page_id, COUNT(DISTINCT t1.friend_user_id)
FROM t1
LEFT JOIN page_likes AS pl
    ON t1.page_id = pl.page_id 
        AND t1.user_id = pl.user_id
WHERE pl.user_id IS NULL 
GROUP BY 1

Can I also perform same query above without a cte and just join same table again?

SELECT t1.user_id, t1.page_id, COUNT(DISTINCT t1.friend_user_id)
    FROM  friends f
    INNER JOIN page_likes pl
        ON f.friend_user_id = pl.user_id
    LEFT JOIN page_likes AS pll
        ON t1.page_id = pll.page_id 
            AND t1.user_id = pll.user_id
    WHERE pll.user_id IS NULL 
    GROUP BY 1

thanks!

Answer 1

Why just not :

SELECT f.user_id,pl.page_id,COUNT(*) friends_page_like_count
FROM friends f
INNER JOIN page_likes pl ON f.friend_user_id = pl.user_id
GROUP BY f.user_id,pl.page_id