1

Please login or register to vote for this query.

(click on this box to dismiss)

Users with so "bad" down/up ratio, that all users with more posts have only better. It is useful on the metas.

Q&A about the site for scholars and enthusiasts of literature

WITH UserPosts AS (
  SELECT
    OwnerUserId AS UserId,
    COUNT(*) AS PostCount
  FROM Posts
  WHERE
    OwnerUserId > 0
    AND
    PostTypeId BETWEEN 1 AND 2
  GROUP BY OwnerUserId
),
DownRatio AS (
  SELECT
    Posts.OwnerUserId AS UserId,
    COUNT(CASE WHEN Votes.VoteTypeId = 2 THEN 1 END) AS Ups,
    COUNT(CASE WHEN Votes.VoteTypeId = 3 THEN 1 END) AS Downs,
    100.0 * COUNT(CASE WHEN Votes.VoteTypeId = 3 THEN 1 END)
      / COUNT(*) AS DownRatio
  FROM Posts, Votes
  WHERE
    Posts.OwnerUserId > 0
    AND
    Votes.PostId = Posts.Id
    AND
    Posts.PostTypeId BETWEEN 1 AND 2
    AND
    Votes.VoteTypeId BETWEEN 2 AND 3
  GROUP BY Posts.OwnerUserId
),
Raw AS (
  SELECT
    UserPosts.UserId,
    UserPosts.PostCount,
    DownRatio.Ups,
    DownRatio.Downs,
    DownRatio.DownRatio,
    MAX(DownRatio.DownRatio)
      OVER (ORDER BY UserPosts.PostCount DESC) AS Max
  FROM UserPosts, DownRatio
  WHERE
    UserPosts.UserId = DownRatio.UserId
    AND
    DownRatio.DownRatio < 100.0
)
SELECT
  UserId AS [User Link],
  PostCount,
  Ups,
  Downs,
  DownRatio
FROM Raw
WHERE DownRatio = Max
ORDER BY PostCount DESC, UserId

fork query

edited may 25

created may 24 peterh - PLEASE DELETE THIS ACCOUNT