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.

Q&A for engineers, scientists, programmers, and computing professionals interested in quantum computing

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

fork query

edited nov 17 19

created nov 16 19 peterh (2)