0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people who build and repair bicycles, people who train cycling, or commute on bicycles

WITH CommentCounts AS (
    SELECT 
        c.PostId AS AnswerId,  
        c.UserId AS CommentorId, 
        COUNT(DISTINCT c.Id) AS CommentCount
    FROM Comments c
    WHERE c.PostId IN (34784842, 20795628)
    GROUP BY c.PostId, c.UserId
)
SELECT DISTINCT 
    p.Id AS AnswerId,                -- Answer ID (from Posts table)
    p.OwnerUserId AS AnswerAuthorId,  -- Answer Author ID (from Posts table)
    c.UserId AS CommentorId,          -- Commentor ID (can be NULL if no comments)
    c.Id AS CommentId,                -- Comment ID (can be NULL if no comments)
    c.CreationDate AS CommentDate,    -- Comment Creation Date (can be NULL if no comments)
    v.VoteDate,                       -- Vote Date (can be NULL if no votes)
    v.VoteTypeId,                     -- Vote Type ID (can be NULL if no votes)
    u.DisplayName AS CommentorName,   -- Commentor Name (can be NULL if no comments)
    u.Reputation AS CommentorReputation, -- Commentor Reputation (can be NULL if no comments)
    COALESCE(cc.CommentCount, 0) AS CommentCount, -- Comment Count, defaulting to 0 if no comments
    c.Text AS CommentBody,            -- Comment Body (can be NULL if no comments)
    CASE 
        WHEN p.OwnerUserId != c.UserId THEN 'Different' ELSE 'Same' 
    END AS IsDifferentAuthor          -- Check if Answer Author and Commentor are different (can be NULL if no comments)
FROM Posts p
LEFT JOIN Comments c ON p.Id = c.PostId AND c.PostId IN (34784842, 20795628)  -- Left join with Comments table
LEFT JOIN CommentCounts cc ON c.PostId = cc.AnswerId AND c.UserId = cc.CommentorId -- Left join with CommentCounts
LEFT JOIN (
    SELECT 
        v.PostId, 
        MIN(v.CreationDate) AS VoteDate, 
        v.VoteTypeId
    FROM Votes v
    WHERE v.PostId IN (34784842, 20795628)
    AND v.VoteTypeId = 3  -- Downvotes on the answer
    GROUP BY v.PostId, v.VoteTypeId
) v ON p.Id = v.PostId  -- Left join with Votes table to get VoteDate and VoteTypeId
LEFT JOIN Users u ON c.UserId = u.Id  -- Left join with Users table to get commentor info
WHERE p.Id IN (34784842, 20795628)
GROUP BY 
    p.Id,               -- Grouping by Answer ID (PostId)
    p.OwnerUserId,      -- Grouping by Answer Author ID
    c.UserId,           -- Grouping by Commentor ID
    c.Id,               -- Grouping by Comment ID
    c.CreationDate,     -- Grouping by Comment Creation Date
    v.VoteDate,         -- Grouping by Vote Date
    v.VoteTypeId,       -- Grouping by Vote Type ID
    u.DisplayName,      -- Grouping by Commentor Name
    u.Reputation,       -- Grouping by Commentor Reputation
    cc.CommentCount,    -- Grouping by Comment Count
    c.Text              -- Grouping by Comment Body

fork query

edited sep 20

created sep 20 anonymous