Questions with most views but least votes

1

Please login or register to vote for this query.

(click on this box to dismiss)

Ask Ubuntu

Q&A for Ubuntu users and developers

WITH Raw AS (
  SELECT
    PostsWithDeleted.Id,
    MIN(PostsWithDeleted.ViewCount) AS ViewCount,
    COUNT(*) AS VoteNo
  FROM PostsWithDeleted, Votes
  WHERE
    PostsWithDeleted.PostTypeId = 1
    AND
    Votes.PostId = PostsWithDeleted.Id
    AND
    Votes.VoteTypeId IN (2, 3)
  GROUP BY PostsWithDeleted.Id
),
Raw2 AS (
  SELECT
    Id, ViewCount, VoteNo,
    DENSE_RANK() OVER (ORDER BY ViewCount DESC) AS ViewsRank,
    DENSE_RANK() OVER (ORDER BY VoteNo) AS VoteRank
  FROM Raw
)
SELECT TOP 100 Id AS [Post Link], *
FROM Raw2
ORDER BY ViewsRank + VoteRank

Enter Parameters

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