Get pages

0

Please login or register to vote for this query.

(click on this box to dismiss)

Web Applications

Q&A for power users of web applications

WITH TopQuestions AS (
    SELECT 
        Id,
        Title,
        CreationDate,
        Score
    FROM Posts
    WHERE PostTypeId = 1 -- Question
        AND DATEDIFF(year, CreationDate, GETDATE()) <= 1
    ORDER BY Score DESC, CreationDate DESC
    OFFSET 200000 ROWS
    FETCH NEXT 1 ROWS ONLY
),
BestAnswers AS (
    SELECT
        ParentId,
        MAX(Score) AS MaxScore
    FROM Posts
    WHERE PostTypeId = 2 -- Answer
    GROUP BY ParentId
),
QuestionTags AS (
    SELECT
        PT.Id,
        STRING_AGG(PT.Tag, '|') WITHIN GROUP (ORDER BY PT.Tag) AS Tags
    FROM (
        SELECT
            Id,
            REPLACE(REPLACE(Tag, '<', ''), '>', '') AS Tag
        FROM Posts
        CROSS APPLY (SELECT value FROM STRING_SPLIT(Tags, '>')) AS T(Tag)
    ) PT
    GROUP BY PT.Id
)
SELECT
    TQ.Id AS Id,
    TQ.Title AS Question,
    TQ.CreationDate AS QuestionDate,
    TQ.Score AS Ranking,
    P.Body AS Answer,
    QT.Tags AS Tags
FROM TopQuestions TQ
JOIN BestAnswers BA ON TQ.Id = BA.ParentId
JOIN Posts P ON P.ParentId = BA.ParentId AND P.Score = BA.MaxScore
JOIN QuestionTags QT ON TQ.Id = QT.Id
ORDER BY TQ.Score DESC, TQ.CreationDate;

Enter Parameters

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