1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for expert genealogists and people interested in genealogy or family history

WITH Raw AS (
  SELECT
    PostsWithDeleted.Id AS PostId,
    SUM (
      CASE
        WHEN PostsWithDeleted.PostTypeId = 2 AND Votes.Id = 2 THEN 10
        WHEN Votes.VoteTypeId = 3 THEN -2
        ELSE 5
      END) AS Rep
  FROM PostsWithDeleted, Votes
  WHERE
    Votes.PostId = PostsWithDeleted.Id
    AND
    Votes.VoteTypeId >= 3
    AND
    PostsWithDeleted.DeletionDate IS NOT NULL
  GROUP BY PostsWithDeleted.Id
)
SELECT TOP 100
  Q.Id AS QId,
  'site:///posts/' + CAST(Q.Id AS VARCHAR(MAX)) AS QuestionLink,
  MIN(QRaw.Rep) + SUM(ARaw.Rep) AS RepLoss
FROM PostsWithDeleted Q, Raw AS QRaw, PostsWithDeleted A, Raw AS ARaw
WHERE
  A.ParentId = Q.Id
  AND
  A.DeletionDate IS NOT NULL
  AND
  Q.DeletionDate IS NOT NULL
  AND
  A.DeletionDate = Q.DeletionDate
  AND
  QRaw.PostId = Q.Id
  AND
  ARaw.PostId = A.Id
GROUP BY Q.Id
ORDER BY RepLoss DESC, Q.Id DESC

view current version

edited jul 15 19

created jul 15 19 peterh (2)