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
        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
    qstU.DisplayName AS QuestionDisplayName,       -- Display name of the user who asked the question
    qstU.Reputation AS QuestionReputation,         -- Reputation of the user who asked the question
    qstU.WebsiteUrl AS QuestionWebsiteUrl,         -- Website URL of the user who asked the question
    qstU.Location AS QuestionLocation,             -- Location of the user who asked the question
    qstU.AboutMe AS QuestionAboutMe,               -- 'About Me' section of the user's profile who asked the question
    qstU.ProfileImageUrl AS QuestionProfileImageUrl, -- Profile image URL of the user who asked the question
    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,
    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
    ansU.Location AS AnswerLocation,               -- Location of the user who answered
    ansU.AboutMe AS AnswerAboutMe,                 -- 'About Me' section of the user's profile who answered
    ansU.ProfileImageUrl AS AnswerProfileImageUrl  -- Profile image URL of the user 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 qstU ON q.QuestionUserId = qstU.Id       -- Join with Users table to get details of the user who asked the question
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