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

-- CTE for retrieving questions from the year 2021
WITH questions_2021 AS (
    SELECT 
        Id,                -- Question ID
        Title,             -- Question title
        Body,              -- Content of the question
        CreationDate,      -- Date when the question was posted
        Tags,              -- Tags associated with the question
        AcceptedAnswerId,  -- ID of the accepted answer (if any)
        OwnerUserId,       -- User ID of question owner
        OwnerDisplayName,  -- Display name of question owner
        FavoriteCount,     -- Number of users who "favourited" 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,               -- Answer ID
        ParentId,         -- ID of the question to which this answer belongs
        Body,             -- Content of the answer
        CreationDate,     -- Date when the answer was posted
        Score,            -- Score (upvotes - downvotes) of the answer
        OwnerUserId       -- 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 Score 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.Id AS QuestionId,     -- ID of the question
    q.Title,                -- Title of the question
    q.Body AS QuestionBody, -- Body of the question
    q.CreationDate AS QuestionDate, -- Date of question creation
    q.Tags,                 -- Tags associated with the question
    a.Body AS AnswerBody,   -- Body of the answer
    a.CreationDate AS AnswerDate, -- Date of answer creation
    a.Score AS AnswerScore,       -- Score of the answer
    -- Add a binary flag to indicate whether the answer is accepted (1) or not (0)
    CASE 
        WHEN a.Id = q.AcceptedAnswerId THEN 1 
        ELSE 0 
    END AS IsAccepted,
    u.Id AS UserId,               -- ID of the user who answered
    u.DisplayName,                -- Display name of the user
    u.Reputation,                 -- Reputation of the user
    u.WebsiteUrl,                 -- Website URL of the user (if available)
    u.Location,                   -- Location of the user (if available)
    u.AboutMe,                    -- 'About Me' section of the user's profile
    u.ProfileImageUrl             -- URL of the user's profile image
FROM 
    questions_2021 q              -- From the questions CTE
INNER JOIN 
    top_answers a ON q.Id = a.ParentId AND a.Rank <= 5
                                   -- Join with top_answers CTE to get top 5 answers
INNER JOIN 
    Users u ON a.OwnerUserId = u.Id
                                   -- Join with Users table to get user details
ORDER BY 
    q.CreationDate DESC, a.Rank    -- Order by question date and answer rank

view current version

edited dec 9 23

created dec 9 23 Harshvardhan