WITH ranked_answers AS ( SELECT ParentId, Id, Score, Last...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Arqade

Q&A for passionate videogamers on all platforms

--Sorted By Question Score
WITH ranked_answers AS (
    SELECT ParentId,
          Id,
          Score,
          LastActivityDate,
          ROW_NUMBER() OVER (PARTITION BY ParentId
                             ORDER BY Score DESC) rn
   FROM POSTS a
   WHERE a.PostTypeId = 2
)
SELECT p1.Id,
       p1.Score AS QuestionScore,
       p1.Title,
       acc.Score AS AcceptedScore,
       ra.Score AS HighestScore,
       DATEDIFF(day, ra.LastActivityDate, acc.LastActivityDate) AS AcceptedRelativeAgeInDays,
       CONCAT('https://gaming.stackexchange.com/questions/', p1.Id) AS URL
FROM POSTS p1
  INNER JOIN POSTS acc ON acc.Id = p1.AcceptedAnswerId
  INNER JOIN ranked_answers ra
    ON ra.ParentId = p1.Id
    AND ra.Id != acc.Id
    AND ra.rn = 1
    AND ra.Score > acc.Score
WHERE p1.PostTypeId = 1
ORDER BY p1.Score DESC;

--Sorted By Question View Count
WITH ranked_answers AS (
    SELECT ParentId,
          Id,
          Score,
          LastActivityDate,
          ROW_NUMBER() OVER (PARTITION BY ParentId
                             ORDER BY Score DESC) rn
   FROM POSTS a
   WHERE a.PostTypeId = 2
)
SELECT p1.Id,
       p1.Score AS QuestionScore,
       p1.Title,
       acc.Score AS AcceptedScore,
       ra.Score AS HighestScore,
       DATEDIFF(day, ra.LastActivityDate, acc.LastActivityDate) AS AcceptedRelativeAgeInDays,
       CONCAT('https://gaming.stackexchange.com/questions/', p1.Id) AS URL
FROM POSTS p1
  INNER JOIN POSTS acc ON acc.Id = p1.AcceptedAnswerId
  INNER JOIN ranked_answers ra
    ON ra.ParentId = p1.Id
    AND ra.Id != acc.Id
    AND ra.rn = 1
    AND ra.Score > acc.Score
WHERE p1.PostTypeId = 1
ORDER BY p1.ViewCount DESC;

Enter Parameters

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