Sep26_when_coumntcount_2+

0

Please login or register to vote for this query.

(click on this box to dismiss)

Bicycles

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;

Enter Parameters

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