0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for students, teachers, and linguists wanting to discuss the finer points of the Chinese language

WITH 
VotesMods AS (
    SELECT PostId, 
    SUM(CASE WHEN vt.Name = 'UpMod' THEN 1 ELSE 0 END) AS UpCnt,
    SUM(CASE WHEN vt.Name = 'DownMod' THEN 1 ELSE 0 END) AS DownCnt
    FROM Votes v
    JOIN VoteTypes vt
    ON v.VoteTypeId = vt.Id
    GROUP BY PostId
    ),
RankedVotes AS (
    SELECT 
    PostId,
    DENSE_RANK() OVER (ORDER BY UpCnt DESC) UpRank,
    DENSE_RANK() OVER (ORDER BY DownCnt DESC) DownRank
    FROM VotesMods )
SELECT PostId FROM RankedVotes
WHERE UpRank <= 10
UNION ALL
SELECT PostId FROM RankedVotes
WHERE DownRank <= 10

view current version

edited dec 3

created dec 3 Janis123