188: Model solution is wrong...missing requirement that comment be posted on same day as original post.

Question has 3 parts:

• Write a query to return the top 1 question by each department.
• Rank order the question's popularity by the number of comments on the same day the post was created.
• When counting the number of comments for a question, exclude all the comments made by the author.

The given solution is missing the requirement that the comment be posted on the same day as the post. I've added it this parameter to the example solution, shown below.

Additionally--because this new parameter creates ties (Human Resources have multiple posts with only 1 qualifying comment), we need to account for this by changing ROW_NUMBER to RANK and allow all 3 posts from Human Resources to show (also added below).

WITH questions AS (
    SELECT post_id, F.employee_id AS author_id, created_dt, E.department
    FROM google_forum F
    INNER JOIN google_employee E
    ON E.employee_id = F.employee_id
    WHERE post_type = 'question'
),
comments AS (
    SELECT post_id, employee_id AS commentor_id, created_dt
    FROM google_forum 
    WHERE post_type = 'comment'
),
department_rankings AS (
    SELECT department, post_id, RANK()OVER(PARTITION BY department ORDER BY num_comments DESC) AS ranking    --Change to Rank for ties
    FROM (
        SELECT Q.post_id, department, COUNT(C.post_id) AS num_comments
        FROM questions Q
        INNER JOIN comments C
        ON Q.post_id = C.post_id
        AND DATE(Q.created_dt) = DATE(C.created_at)    --This needs to be added to meet the question's requirements.
        AND Q.author_id != C.commentor_id
        GROUP BY 1, 2
    ) X
)
SELECT department, post_id
FROM department_rankings
WHERE ranking =1;
;