[MySQL] (Medium) Spam Posts

문제: https://platform.stratascratch.com/coding/10134-spam-posts?code_type=3

나의 풀이:

select post_date
     , count(distinct(case when post_keywords like '%spam%' then post_id end)) / count(distinct post_id) * 100 as span_share
from facebook_posts
     join facebook_post_views using (post_id)
group by post_date;

우하항 맞췄다

 

솔루션:

SELECT spam_summary.post_date,
       (n_spam/n_posts)*100 AS spam_share
FROM
  (SELECT post_date,
          sum(CASE
                  WHEN v.viewer_id IS NOT NULL THEN 1
                  ELSE 0
              END) AS n_posts
   FROM facebook_posts p
   JOIN facebook_post_views v ON p.post_id = v.post_id
   GROUP BY post_date) posts_summary
LEFT JOIN
  (SELECT post_date,
          sum(CASE
                  WHEN v.viewer_id IS NOT NULL THEN 1
                  ELSE 0
              END) AS n_spam
   FROM facebook_posts p
   JOIN facebook_post_views v ON p.post_id = v.post_id
   WHERE post_keywords LIKE '%spam%'
   GROUP BY post_date) spam_summary ON spam_summary.post_date = posts_summary.post_date;

..? 왤케 길지..?

 

조인한 테이블: 날짜별로 뷰가 있는 포스트 수 & 날짜별 키워드에 스팸을 포함한 뷰가 있는 포스트 수

날짜를 기준으로 조인

퍼센트 계산

 

흠 이해는 되는데 내 코드도 결국 동일하게 작동하지 않나..! 하는~~