0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for programmers

WITH BountiesOffered as
(
  SELECT 
  COALESCE(SUM(BountyAmount),0) BountyAmount,
     UserID
  FROM
     Votes
     GROUP BY UserID)

,  CTE AS (
SELECT 
 p.OwnerUserID,
 u.Reputation,
 Count(p.id) postCount,
 SUM(Case PostTypeID WHEN 1 THEN 1 ELSE 0 END) [Question Count],
 SUM(Case PostTypeID WHEN 2 THEN 1 ELSE 0 END) [Answer Count]
FROM
  posts P 
  INNER JOIN Users u
  ON OwnerUserID = u.id
  
WHERE
  u.id <> -1 --Community user
  and
  u.Reputation <> 1 --Suspended user

GROUP BY 
  p.OwnerUserID,
  u.Reputation
HAVING
  Count(p.id) > 100  
)
SELECT 
RANK() OVER (ORDER BY  CAST(c.Reputation + b.BountyAmount AS DECIMAL  ) / PostCount) Rank,
    c.OwnerUserID [User Link],
    c.Reputation + b.BountyAmount [Rep plus bounties offered],
    c.postCount,
    c.[Question Count],
    c.[Answer Count],
    CAST(c.Reputation + b.BountyAmount AS DECIMAL  ) / PostCount  as [Rep Per Post Bounty Adjusted]
FROM
  cte c
  INNER JOIN BountiesOffered b
  ON c.OwnerUserID = b.UserID
ORDER BY 
  CAST(c.Reputation + b.BountyAmount AS DECIMAL  ) / PostCount ,
  OwnerUserID

fork query

edited jan 3

created jan 3 conradfrix