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 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

view current version

edited apr 25 13 JNK

created apr 25 13 anonymous