Q&A for people who build and repair bicycles, people who train cycling, or commute on bicycles
-- Step 1: Retrieve downvotes for the specific answers (PostIds) SELECT v.Id AS VoteId, v.PostId AS AnswerId, -- This is the specific answer ID v.CreationDate AS VoteDate, -- Include the vote creation date COUNT(v.Id) OVER (PARTITION BY v.PostId) AS downvote_count -- Calculate total downvotes for each AnswerId INTO #Downvotes FROM Votes v WHERE v.VoteTypeId = 3 AND v.PostId IN ( 412499, 5155064, 14186333, 14528940, 25957456, 30635615, 33854963, 34769186, 36184192, 47537787, 51673759, 52819365, 70668065, 3614515, 14117002, 17284149, 17335326, 21897585, 30653545, 33258221, 35693203, 42365034, 42755494, 3331372, 13478143, 22640949, 24869361, 25661346, 27491718, 76605477, 9311585, 28701875, 7286526, 12448660, 30364967, 43876351, 45374162, 18977525, 26201470, 32713411, 45761277, 28787850, 34784842, 15747107, 49687760, 3433300, 27574013, 21550325, 19507833, 24393769, 16326307, 20795628, 22616491, 2068371, 67629882 ); -- The full list of specific answer IDs -- Step 2: Retrieve comments related to the specified answers (PostIds) SELECT c.Id AS CommentId, c.PostId AS AnswerId, -- Include the AnswerId for clarity c.UserId AS CommentorId, c.Text AS CommentBody, -- Include the comment text (body) c.CreationDate AS CommentDate -- Include the comment creation date INTO #Comments FROM Comments c WHERE c.PostId IN ( 412499, 5155064, 14186333, 14528940, 25957456, 30635615, 33854963, 34769186, 36184192, 47537787, 51673759, 52819365, 70668065, 3614515, 14117002, 17284149, 17335326, 21897585, 30653545, 33258221, 35693203, 42365034, 42755494, 3331372, 13478143, 22640949, 24869361, 25661346, 27491718, 76605477, 9311585, 28701875, 7286526, 12448660, 30364967, 43876351, 45374162, 18977525, 26201470, 32713411, 45761277, 28787850, 34784842, 15747107, 49687760, 3433300, 27574013, 21550325, 19507833, 24393769, 16326307, 20795628, 22616491, 2068371, 67629882 ); -- The full list of specific answer IDs -- Step 2.1: Retrieve the answer bodies for the specified answers (PostIds) SELECT p.Id AS AnswerId, p.Body AS AnswerBody -- Include the answer body INTO #AnswerBodies FROM Posts p WHERE p.Id IN ( 412499, 5155064, 14186333, 14528940, 25957456, 30635615, 33854963, 34769186, 36184192, 47537787, 51673759, 52819365, 70668065, 3614515, 14117002, 17284149, 17335326, 21897585, 30653545, 33258221, 35693203, 42365034, 42755494, 3331372, 13478143, 22640949, 24869361, 25661346, 27491718, 76605477, 9311585, 28701875, 7286526, 12448660, 30364967, 43876351, 45374162, 18977525, 26201470, 32713411, 45761277, 28787850, 34784842, 15747107, 49687760, 3433300, 27574013, 21550325, 19507833, 24393769, 16326307, 20795628, 22616491, 2068371, 67629882 ); -- Step 3: Combine the data using LEFT JOIN and calculate the time difference SELECT c.AnswerId, -- Include the AnswerId from comments table d.VoteId, d.VoteDate, -- Include the vote creation date c.CommentId, c.CommentorId, c.CommentBody, -- Include the comment body c.CommentDate, ab.AnswerBody, -- Include the answer body d.downvote_count, -- Include the total downvotes for the answer ABS(DATEDIFF(SECOND, d.VoteDate, c.CommentDate)) AS TimeDifference, -- Calculate the absolute time difference in seconds ROW_NUMBER() OVER (PARTITION BY c.CommentId ORDER BY ABS(DATEDIFF(SECOND, d.VoteDate, c.CommentDate)) ASC) AS RowNum -- Assign row numbers based on the smallest time difference INTO #CombinedData FROM #Comments c LEFT JOIN #Downvotes d ON c.AnswerId = d.AnswerId -- Join using AnswerId (PostId) to include all comments regardless of vote LEFT JOIN #AnswerBodies ab ON c.AnswerId = ab.AnswerId -- Join to include the answer body ORDER BY c.CommentId; -- Step 4: Select only the rows with the smallest time difference for each comment SELECT AnswerId, VoteId, VoteDate, downvote_count, -- Include the total downvote count column CommentId, CommentorId, CommentBody, CommentDate, AnswerBody, -- Include the answer body TimeDifference -- Include the time difference to understand the closeness FROM #CombinedData WHERE RowNum = 1 -- Keep only the first row with the smallest time difference for each CommentId ORDER BY CommentId;