0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for experienced flyers, pilots, and builders of drones and model aircraft

SELECT 
    p.Id AS QuestionId,
    p.Title AS QuestionTitle,
    p.Tags AS TagSet,
    CASE 
        WHEN CHARINDEX('><', p.Tags) > 0 THEN LEFT(p.Tags, CHARINDEX('><', p.Tags) - 1)
        ELSE p.Tags
    END AS PrimaryTag, -- Extracts the first tag or the whole tag if there's only one
    p.Score AS UpVotes, -- Assuming 'Score' is the upvotes count for the question
    p.OwnerUserId AS QuestionUserId, -- User ID of the question asker
    u.Reputation AS UserReputation,
    p.CreationDate AS QuestionCreationTime,
    (
        SELECT MIN(CreationDate) 
        FROM Posts 
        WHERE ParentId = p.Id AND PostTypeId = 2
    ) AS FirstAnswerCreationTime,
    a.CreationDate AS AcceptedAnswerCreationTime, -- Directly joins accepted answer time
    a.Score AS AcceptedAnswerUpVotes, -- Accepted answer upvotes
    (
        SELECT COUNT(*) 
        FROM Votes 
        WHERE PostId = a.Id AND VoteTypeId = 3
    ) AS AcceptedAnswerDownVotes,
    a.OwnerUserId AS AcceptedAnswerUserId, -- User ID of the person who gave the accepted answer
    p.ViewCount AS TotalViews
FROM 
    Posts p
JOIN 
    Users u ON p.OwnerUserId = u.Id
LEFT JOIN 
    Posts a ON p.AcceptedAnswerId = a.Id -- Join the accepted answer
WHERE 
    p.PostTypeId = 1 -- PostTypeId 1 indicates questions
    AND p.CreationDate >= '2022-12-01' 
    AND p.CreationDate < '2024-12-02'
    AND p.AcceptedAnswerId IS NOT NULL; -- Filters out unanswered questions

fork query

edited dec 11

created dec 10 anonymous