Q&A for science fiction and fantasy enthusiasts
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