SQL 라이브 코테 뽀개기 Day2: Page With No Likes

Assume you're given two tables containing data about Facebook Pages and their respective likes (as in "Like a Facebook Page").

Write a query to return the IDs of the Facebook pages that have zero likes. The output should be sorted in ascending order based on the page IDs.

pages Table: 

Column	NameType
page_name	varchar
page_id	integer

pages Example Input:

page_id	page_name
20001	SQL Solutions
20045	Brain Exercises
20701	Tips for Data Analysts

page_likes Table:

Column	NameType
user_id	integer
page_id	integer
liked_date	datetime

page_likes Example Input:

user_id	page_id	liked_date
111	20001	04/08/2022 00:00:00
121	20045	03/12/2022 00:00:00
156	20001	07/25/2022 00:00:00

Example Output:

page_id

20701

LEFT JOIN 사용하기

SELECT p.page_id
FROM pages p 
FULL OUTER JOIN page_likes pl
ON p.page_id = pl.page_id
WHERE pl.user_id IS NULL
ORDER BY page_id;

 

EXCEPT 사용하기: 신기하다!!!

SELECT page_id
FROM pages
EXCEPT
SELECT page_id
FROM page_likes;

 

NOT IN  사용하기

SELECT page_id
FROM pages
WHERE page_id NOT IN (
  SELECT page_id
  FROM page_likes
  WHERE page_id IS NOT NULL -- 없어도 결과 같음
 )

 

NOT EXISTS 사용하기

SELECT page_id
FROM pages p
WHERE NOT EXISTS (
  SELECT page_id
  FROM page_likes pl
  WHERE pl.page_id = p.page_id
  )

 

CTE 사용하기

WITH count_likes AS (
  SELECT p.page_id, 
    COUNT(pl.user_id) likes
  FROM pages p
  LEFT JOIN page_likes pl
  ON p.page_id = pl.page_id
  GROUP BY p.page_id
  HAVING COUNT(pl.user_id) = 0
)
SELECT page_id
FROM count_likes;

•  CTE 만들기:  count_likes
  • pages 테이블에 page_id  기준으로 page_likes 테이블을 LEFT JOIN 한다.
  • page_id별로 그룹핑 한 후, user_id 수가 0인 경우만 필터링하여 추출한다. 
• count_likes에서 page_id만 추출한다.