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