1

Please login or register to vote for this query.

(click on this box to dismiss)

It counts only the votes, the bounties are ignored. (And it ignores also the -100 from 6 spam/rude flags.)

Q&A for people interested in the history and origins of science and mathematics

WITH SelfAnswered AS (
  SELECT
    Q.Id AS QId,
    A.Id AS AId,
    Q.OwnerUserId AS UserId
  FROM Posts Q, Posts A
  WHERE
    A.ParentId = Q.Id
    AND
    Q.PostTypeId = 1
    AND
    A.PostTypeId = 2
    AND
    Q.OwnerUserId = A.OwnerUserId
),
VC AS (
  SELECT
    PostId,
    VoteTypeId,
    COUNT(*) AS N
  FROM Votes
  WHERE VoteTypeId IN (2, 3)
  GROUP BY PostId, VoteTypeId
),
Raw AS (
  SELECT
    AId,
    SelfAnswered.UserId,
    COALESCE(QUp.N, 0) AS QUp,
    COALESCE(QDown.N, 0) AS QDown,
    COALESCE(AUp.N, 0) AS AUp,
    COALESCE(ADown.N, 0) AS ADown
  FROM SelfAnswered
  LEFT JOIN VC AS QUp
    ON SelfAnswered.QId = QUp.PostId AND QUp.VoteTypeId = 2
  LEFT JOIN VC AS QDown
    ON SelfAnswered.QId = QDown.PostId AND QDown.VoteTypeId = 3
  LEFT JOIN VC AS AUp
    ON SelfAnswered.AId = AUp.PostId AND AUp.VoteTypeId = 2
  LEFT JOIN VC AS ADown
    ON SelfAnswered.AId = ADown.PostId AND ADown.VoteTypeId = 3
),
RawRep AS (
  SELECT *, QUp*5 - QDown*2 + AUp*10 - ADown*2 AS Rep FROM Raw
)
SELECT
  AId AS [Post Link],
  UserId AS [User Link],
  Users.Reputation,
  RawRep.*
FROM RawRep
LEFT JOIN Users ON Users.Id = RawRep.UserId
WHERE Rep < 0
ORDER BY Users.Reputation, Rep

fork query

edited jul 6 17

created may 18 17 peterh