Answer score histogram before, is, after the accepted answer

1

Please login or register to vote for this query.

(click on this box to dismiss)

Role-playing Games

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

WITH Raw AS (
  SELECT
    COALESCE(SIGN(A.Id - Q.AcceptedAnswerId), 2) AS BeforeAfter,
    A.Score,
    COUNT(*) AS Count
  FROM Posts Q, Posts A
  WHERE
    A.ParentId = Q.Id
    AND
    A.Score >= ##MinScore:int?-3##
    AND
    A.Score <= ##MaxScore:int?10##
    GROUP BY COALESCE(SIGN(A.Id - Q.AcceptedAnswerId), 2), A.Score
),
Raw2 AS (
  SELECT BeforeAfter, SUM(Count) AS Count
  FROM Raw
  GROUP BY BeforeAfter
)
SELECT
  Score,
  CASE
    WHEN Raw.BeforeAfter = 2 THEN 'NoAccepted'
    WHEN Raw.BeforeAfter = -1 THEN 'BeforeAccepted'
    WHEN Raw.BeforeAfter = 0 THEN 'IsAccepted'
    WHEN Raw.BeforeAfter = 1 THEN 'AfterAccepted'
    ELSE NULL -- unreachable code
  END AS [When],
  100.0 * Raw.Count / Raw2.Count AS Pct
FROM Raw, Raw2
WHERE
  Raw.BeforeAfter = Raw2.BeforeAfter
ORDER BY Raw.Score, Raw.BeforeAfter

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results