Q&A for students, teachers, and linguists wanting to discuss the finer points of the Russian language
WITH QRep AS ( SELECT P.OwnerUserID, SUM(CASE WHEN V.VotetypeId = 2 THEN 5 WHEN V.VoteTypeId = 3 THEN -2 ELSE 0 END) as 'QRep' FROM Posts P INNER JOIN votes v ON v.PostID = P.ID WHERE P.PostTypeId = 1 GROUP BY P.OwnerUserId ) , ARep AS ( SELECT P.OwnerUserID, SUM(CASE WHEN V.VotetypeId = 2 THEN 10 WHEN V.VoteTypeId = 3 THEN -2 WHEN v.VoteTypeId = 1 THEN 15 ELSE 0 END) as 'ARep' FROM Posts P INNER JOIN votes v ON v.PostID = P.ID WHERE P.PostTypeId = 2 GROUP BY P.OwnerUserId ) SELECT TOP 100 'Http://www.dba.stackexchage.com/users/' + CAST(u.Id as varchar(100)) ,u.displayname ,Cxr.Rep ,PostCt ,RepPerPost FROM Users u INNER JOIN QRep Q ON Q.OwnerUserID = u.ID INNER JOIN Arep A ON A.OwnerUserID = u.ID CROSS APPLY (SELECT A.Arep+Q.Qrep) Cxr(Rep) CROSS APPLY (SELECT COUNT(*) FROM Posts WHERE OwnerUserId = u.id) CxA(PostCt) CROSS APPLY (SELECT CAST(Cxr.rep as decimal (10,2))/Cxa.PostCt) CxB(RepPerPost) WHERE PostCt > 0 AND u.reputation > 1 AND Cxr.Rep > 100 ORDER BY RepPerPost ASC