WITH max_comment_dates AS ( SELECT postid, MAX(creationda...

0

Please login or register to vote for this query.

(click on this box to dismiss)

English Language Learners

Q&A for speakers of other languages learning English

WITH max_comment_dates AS (
    SELECT postid, MAX(creationdate) AS max_creationdate
    FROM comments
    WHERE text IS NOT NULL
    GROUP BY postid
),
latest_comments AS (
    SELECT c.postid, c.text AS latest_comment
    FROM comments c
    JOIN max_comment_dates 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
    )
)
SELECT count(*)
FROM latest_comments;

-- total comments 30396666
-- total comments 30396780 old query

-- 75632691
-- 76079476
-- 28645383

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results