Q&A for speakers of other languages learning English
WITH max_comment_dates_final AS ( SELECT postid, MAX(creationdate) AS max_creationdate FROM comments WHERE text IS NOT NULL GROUP BY postid ), latest_comments_final AS ( SELECT c.postid, id, c.text AS latest_comment FROM comments c JOIN max_comment_dates_final mcd ON c.postid = mcd.postid AND c.creationdate = mcd.max_creationdate WHERE c.text IS NOT NULL -- Tiebreaker: select the comment with the highest ID in case of ties on creationdate AND c.id = ( SELECT MAX(inner_c.id) FROM comments inner_c WHERE inner_c.postid = c.postid AND inner_c.creationdate = c.creationdate ) ), max_comment_dates_no_filter AS ( SELECT postid, MAX(creationdate) AS max_creationdate FROM comments WHERE text IS NOT NULL GROUP BY postid ), latest_comments_no_filter AS ( SELECT c.postid, id, c.text AS latest_comment FROM comments c JOIN max_comment_dates_no_filter mcd ON c.postid = mcd.postid AND c.creationdate = mcd.max_creationdate WHERE c.text IS NOT NULL ) SELECT count(*) FROM latest_comments_final left join latest_comments_no_filter on latest_comments_final.postid = latest_comments_no_filter.postid where latest_comments_final.id != latest_comments_no_filter.id -- total comments 30396666 -- total comments 30396780 old query -- 75632691 -- 76079476 -- 28645383