Post changed score sign most times

1

Please login or register to vote for this query.

(click on this box to dismiss)

Moderators

Q&A for community managers, administrators, and moderators

WITH Raw AS (
  SELECT PostId, Id AS VoteId, SUM(5-2*VoteTypeId)
    OVER (
      PARTITION BY PostId
      ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS ScoreNow
  FROM Votes
  WHERE
    VoteTypeId IN (2, 3)
), Raw2 AS (
  SELECT
    PostId, VoteId, ScoreNow,
    RANK() OVER (PARTITION BY PostId ORDER BY VoteId) AS N
  FROM Raw
  WHERE ScoreNow IN (-1, 1)
)
SELECT TOP 100
  Prev.PostId AS [Post Link],
  'site:///posts/' + CAST(Prev.PostId AS VARCHAR(MAX)) + '/timeline|'
    + CAST(Prev.PostId AS VARCHAR(MAX)) AS Timeline,
  COUNT(*) AS Count
FROM Raw2 Prev, Raw2 Next
WHERE
  Prev.PostId = Next.PostId
  AND
  Prev.N + 1 = Next.N
  AND
  Prev.ScoreNow * Next.ScoreNow = -1
GROUP BY Prev.PostId
ORDER BY Count DESC

Enter Parameters

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