Big ignored ones

1

Please login or register to vote for this query.

(click on this box to dismiss)

Heuristically get people with the most posts, but least votes

Motor Vehicle Maintenance and Repair Meta

Q&A about the site for mechanics and DIY enthusiast owners of cars, trucks, and motorcycles

WITH UserVotesRaw AS (
  SELECT Posts.OwnerUserId AS UserId, COUNT(*) AS Count
  FROM Posts, Votes
  WHERE
    Votes.PostId = Posts.Id
    AND
    Posts.PostTypeId IN (1, 2)
    AND
    Votes.VoteTypeId IN (2, 3)
  GROUP BY Posts.OwnerUserId
),
UserPostsRaw AS (
  SELECT Posts.OwnerUserId AS UserId, COUNT(*) AS Count
  FROM Posts
  WHERE
    Posts.PostTypeId IN (1, 2)
  GROUP BY Posts.OwnerUserId
),
UserVotes AS (
  SELECT
    DENSE_RANK() OVER (ORDER BY Count) AS VoteRank,
    *
  FROM UserVotesRaw
),
UserPosts AS (
  SELECT
    DENSE_RANK() OVER (ORDER BY Count DESC) AS PostRank,
    *
  FROM UserPostsRaw
)
SELECT TOP 1000
  UserVotes.UserId AS [User Link],
  UserVotes.UserId,
  UserPosts.Count AS Posts,
  UserVotes.Count AS Votes,
  UserPosts.PostRank,
  UserVotes.VoteRank
FROM UserVotes, UserPosts
WHERE
  UserVotes.UserId = UserPosts.UserId
ORDER BY VoteRank + PostRank;

Enter Parameters

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