Should have "Unsung Hero", but has not

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @now DATETIME = (SELECT MAX(CreationDate) FROM Posts);

WITH Raw AS (
  SELECT
    A.OwnerUserId AS UserId,
    COUNT(*) AS TotalAccepted,
    COUNT(CASE WHEN A.Score = 0 THEN 1 END) AS ZeroScore
  FROM Posts Q, Posts A
  WHERE
    Q.AcceptedAnswerId = A.Id
    AND
    A.CommunityOwnedDate IS NULL
    AND
    A.OwnerUserId <> Q.OwnerUserId
    AND
    A.OwnerUserId > 0
    AND
    A.OwnerUserId NOT IN (
      SELECT UserId FROM Badges WHERE Name = 'Unsung Hero'
    )
    AND
    A.CreationDate < DATEADD(d, -10, @now)
  GROUP BY A.OwnerUserId
)
SELECT
  Raw.UserId AS [User Link],
  Users.Reputation,
  Raw.TotalAccepted,
  Raw.ZeroScore,
  100.0 * Raw.ZeroScore / Raw.TotalAccepted AS ZeroScorePct
FROM Raw, Users
WHERE
  Raw.UserId = Users.Id
  AND
  Raw.ZeroScore > 10
  AND
  Raw.TotalAccepted <= Raw.ZeroScore * 4
ORDER BY ZeroScorePct DESC, Reputation, Raw.UserId

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results