Largest reversals

1

Please login or register to vote for this query.

(click on this box to dismiss)

Posts whose maximal score (ever) and minimal score (ever) has different sign and the most distant.

Robotics Meta

Q&A about the site for professional robotic engineers, hobbyists, researchers and students

WITH Raw AS (
  SELECT
    PostsWithDeleted.Id AS PostId,
    RANK() OVER (PARTITION BY PostsWithDeleted.Id ORDER BY Votes.Id) AS VoteNo,
    SUM(5 - 2*Votes.VoteTypeId) OVER (
      PARTITION BY PostsWithDeleted.Id ORDER BY Votes.Id
      ROWS UNBOUNDED PRECEDING
    ) AS ScoreNow
  FROM PostsWithDeleted, Votes
  WHERE
    Votes.PostId = PostsWithDeleted.Id
    AND
    Votes.VoteTypeId BETWEEN 2 AND 3
),
Raw2 AS (
  SELECT
    PostId,
    MIN(ScoreNow) AS MinScore,
    MAX(ScoreNow) AS MaxScore
  FROM Raw
  GROUP BY PostId
  HAVING
    MIN(ScoreNow) < 0
    AND
    MAX(ScoreNow) > 0
)
SELECT TOP 100
  PostId AS [Post Link],
  PostId,
  MinScore,
  MaxScore,
  MaxScore - MinScore AS Diff,
  CASE WHEN -MinScore > MaxScore THEN MaxScore ELSE -MinScore END AS LoseWin,
  'query://1150488/?PostId=' + CAST(PostId AS VARCHAR(MAX)) + '#graph|Graph'
    AS Graph
FROM Raw2
ORDER BY LoseWin DESC, PostId

Enter Parameters

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