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

Q&A for Bitcoin crypto-currency enthusiasts

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;

fork query

edited mar 22 19

created mar 22 19 peterh (2)