[StrataScratch - SQL] (Medium) Spam Posts

문제

 

날짜별로 조회된 전체 게시물 중 스팸 게시물의 비율을 계산하십시오. 게시물의 키워드에 문자열 'spam'이 포함되어 있으면 해당 게시물은 스팸으로 간주됩니다. facebook_posts 테이블은 사용자가 게시한 모든 게시물을 저장합니다. facebook_post_views 테이블은 사용자가 게시물을 조회했는지를 나타내는 액션 테이블입니다.

 

테이블설명

 

예상결과

 

✏️ 풀이

WITH cnt_post AS (
    SELECT post_date, COUNT(viewer_id) AS cnt_p
    FROM facebook_posts p
    JOIN facebook_post_views v ON p.post_id = v.post_id
    GROUP BY 1
), cnt_spam AS (
    SELECT post_date, COUNT(CASE
                        WHEN post_keywords LIKE '%spam%' THEN p.post_id
                        END) AS cnt_s
    FROM facebook_posts AS p
    JOIN facebook_post_views AS c ON p.post_id = c.post_id
    GROUP BY 1
)

SELECT p.post_date, (cnt_s / cnt_p) * 100 AS spam_share
FROM cnt_post AS p
JOIN cnt_spam AS s ON p.post_date = s.post_date;

1. 전체 게시물 카운트

2. 스팸 게시물 카운트

3. 스팸 게시물 비율 계산