172: Model solution is technically correct, but it's non-intuitive and should be revised.

This solution is counting comment rows as content items in the distribution, even though comments cannot receive comments themselves.

This results in an output of: num_comments = 0, num_contents = 6 …when a more logical output would be: num_comments = 0, num_contents = 1.

The delta in the two outputs (the extra 5 items) comes from counting the 5 comments that are technically “content” itmes in the table...but they are not valid content items that can be commented on. Thus, they should be excluded from the distribution of “content by number of comments.”

To fix this inconsistency, we only need to add one where clause to exclude comments as content items.

WITH comments_cnt as (
    SELECT A.content_id, SUM(case when b.target_id IS NULL THEN 0 ELSE 1 END ) AS num_comments
    FROM exercise.content_action A
    LEFT JOIN exercise.content_action B
    ON A.content_id= B.target_id
    WHERE a.content_type != 'comment'   --This is new and solves it.
    GROUP BY A.content_id
)
SELECT num_comments, COUNT(content_id) AS num_contents
FROM comments_cnt
GROUP BY num_comments;