all cross validated questions in 2022

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

-- SEDE Query for Stack Exchange (Adjust for the correct site)
-- This query gets the top 5 answers for each question from 2022

-- Get questions from 2022
WITH questions_2022 AS (
    SELECT 
        Id, Title, Body, CreationDate, Tags, AcceptedAnswerId
    FROM 
        Posts
    WHERE 
        YEAR(CreationDate) = 2022
        AND PostTypeId = 1 -- 1 for Questions
),
-- Get answers from 2022
answers_2022 AS (
    SELECT 
        Id, ParentId, Body, CreationDate, Score, OwnerUserId
    FROM 
        Posts
    WHERE 
        YEAR(CreationDate) = 2022
        AND PostTypeId = 2 -- 2 for Answers
),
-- Get top 5 answers for each question
top_answers AS (
    SELECT *, 
        ROW_NUMBER() OVER(PARTITION BY ParentId ORDER BY Score DESC) AS Rank
    FROM 
        answers_2022
)
SELECT 
    q.Id AS QuestionId, q.Title, q.Body AS QuestionBody, q.CreationDate AS QuestionDate, q.Tags,
    CASE 
        WHEN a.Id = q.AcceptedAnswerId THEN 'ACCEPTED RESPONSE ' + a.Body 
        ELSE a.Body 
    END AS AnswerBody,
    a.CreationDate AS AnswerDate, a.Score AS AnswerScore,
    u.Id AS UserId, 
    u.DisplayName, u.Reputation, u.WebsiteUrl, 
    u.Location, u.AboutMe, u.ProfileImageUrl
FROM 
    questions_2022 q
INNER JOIN 
    top_answers a ON q.Id = a.ParentId AND a.Rank <= 5
INNER JOIN 
    Users u ON a.OwnerUserId = u.Id
ORDER BY 
    q.CreationDate DESC, a.Rank

Enter Parameters

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