Get all CV questions from 2021 with answers

0

Please login or register to vote for this query.

(click on this box to dismiss)

Drones and Model Aircraft Meta

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

-- Define Common Table Expressions (CTEs) to structure the query

-- CTE for retrieving questions from the year 2021
WITH questions_2021 AS (
    SELECT 
        Id AS QuestionId,             -- Question ID
        Title AS QuestionTitle,       -- Question title
        Body AS QuestionBody,         -- Content of the question
        CreationDate AS QuestionDate, -- Date when the question was posted
        Tags AS QuestionTags,         -- Tags associated with the question
        AcceptedAnswerId,             -- ID of the accepted answer (if any)
        OwnerUserId AS QuestionUserId,       -- User ID of question owner
        OwnerDisplayName AS QuestionDisplayName, -- Display name of question owner
        FavoriteCount,                -- Number of users who "favorited" a question
        AnswerCount,                  -- Number of answers to the question
        CommentCount                  -- Number of comments on the question
    FROM 
        Posts                          -- Table containing all posts (questions and answers)
    WHERE 
        YEAR(CreationDate) = 2021 AND  -- Filter to include only posts from 2021
        PostTypeId = 1                 -- 1 indicates the post is a question
),

-- CTE for retrieving answers from the year 2021
answers_2021 AS (
    SELECT 
        Id AS AnswerId,                -- Answer ID
        ParentId,                      -- ID of the question to which this answer belongs
        Body AS AnswerBody,            -- Content of the answer
        CreationDate AS AnswerDate,    -- Date when the answer was posted
        Score AS AnswerScore,          -- Score (upvotes - downvotes) of the answer
        OwnerUserId AS AnswerUserId    -- User ID of the answer's author
    FROM 
        Posts                          -- Table containing all posts
    WHERE 
        YEAR(CreationDate) = 2021 AND  -- Filter to include only posts from 2021
        PostTypeId = 2                 -- 2 indicates the post is an answer
),

-- CTE for selecting top 5 answers for each question based on score
top_answers AS (
    SELECT *,
        ROW_NUMBER() OVER(PARTITION BY ParentId ORDER BY AnswerScore DESC) AS Rank
                                    -- Assigns a rank to each answer within each question
                                    -- based on the score, highest score gets Rank 1
    FROM 
        answers_2021
)

-- Final SELECT statement to retrieve the combined data
SELECT 
    q.QuestionId,                    -- ID of the question
    q.QuestionTitle,                 -- Title of the question
    q.QuestionBody,                  -- Body of the question
    q.QuestionDate,                  -- Date of question creation
    q.QuestionTags,                  -- Tags associated with the question
    q.QuestionUserId,                -- User ID of the question owner
    q.QuestionDisplayName,           -- Display name of the question owner
    q.FavoriteCount,                 -- Number of times the question was favorited
    q.AnswerCount,                   -- Number of answers to the question
    q.CommentCount,                  -- Number of comments on the question
    a.AnswerId,                      -- ID of the answer
    a.AnswerBody,                    -- Body of the answer
    a.AnswerDate,                    -- Date of answer creation
    a.AnswerScore,                   -- Score of the answer
    -- Add a binary flag to indicate whether the answer is accepted (1) or not (0)
    CASE 
        WHEN a.AnswerId = q.AcceptedAnswerId THEN 1 
        ELSE 0 
    END AS IsAccepted,
    a.AnswerUserId,                  -- User ID of the user who answered
    ansU.DisplayName AS AnswerDisplayName, -- Display name of the user who answered
    ansU.Reputation AS AnswerReputation,    -- Reputation of the user who answered
    ansU.WebsiteUrl AS AnswerWebsiteUrl,    -- Website URL of the user who answered (if available)
    ansU.Location AS AnswerLocation,        -- Location of the user who answered (if available)
    ansU.AboutMe AS AnswerAboutMe,          -- 'About Me' section of the user's profile who answered
    ansU.ProfileImageUrl AS AnswerProfileImageUrl -- URL of the user's profile image who answered
FROM 
    questions_2021 q                -- From the questions CTE
INNER JOIN 
    top_answers a ON q.QuestionId = a.ParentId AND a.Rank <= 5
                                   -- Join with top_answers CTE to get top 5 answers
INNER JOIN 
    Users ansU ON a.AnswerUserId = ansU.Id
                                   -- Join with Users table to get details of the user who answered
ORDER BY 
    q.QuestionDate DESC, a.Rank    -- Order by question date and answer rank

Enter Parameters

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