0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for serious players and enthusiasts of chess

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
    u.DisplayName AS QuestionUser,
    u.Reputation AS UserReputation,
    p.CreationDate AS QuestionCreationTime,
    (
        SELECT MIN(CreationDate) 
        FROM Posts 
        WHERE ParentId = p.Id AND PostTypeId = 2
    ) AS FirstAnswerCreationTime,
    (
        SELECT CreationDate 
        FROM Posts 
        WHERE Id = p.AcceptedAnswerId
    ) AS AcceptedAnswerCreationTime,
    (
        SELECT Score 
        FROM Posts 
        WHERE Id = p.AcceptedAnswerId
    ) AS AcceptedAnswerUpVotes,
    (
        SELECT COUNT(*) 
        FROM Votes 
        WHERE PostId = p.AcceptedAnswerId AND VoteTypeId = 3
    ) AS AcceptedAnswerDownVotes,
    p.ViewCount AS TotalViews
FROM 
    Posts p
JOIN 
    Users u ON p.OwnerUserId = u.Id
WHERE 
    p.PostTypeId = 1; -- PostTypeId 1 indicates questions

fork query

edited dec 11

created dec 10 anonymous