RepPerWeek

0

Please login or register to vote for this query.

(click on this box to dismiss)

Worldbuilding

Q&A for writers/artists using science, geography and culture to construct imaginary worlds and settings

WITH UserInfo AS (
       SELECT displayName, 
              id, 
              reputation,
              DATEDIFF(dd, creationDate, GETDATE()) as days
       FROM   users u),
     MoreUserInfo AS (
       SELECT *, 
              reputation/days as repPerDay,
              ((reputation * 7)/days) as repPerWeek
       FROM   UserInfo),
     WeeklyPostSum AS(
       select owneruserid, 
         SUM(case when posttypeid=1 then (score * 5) 
               when posttypeid=2 then score * 10 
               else 0 end) as repLastWeek
       FROM   posts
       WHERE  DATEDIFF(dd, creationDate, GETDATE()) >= 7
       AND    DATEDIFF(dd, creationDate, GETDATE()) < 14
       GROUP BY ownerUserId)
SELECT   mui.displayName as name,
         mui.reputation as rep,
         mui.days as daysSinceJoined,
         mui.repPerDay,
         mui.repPerWeek,
         wps.repLastWeek,
         wps.repLastWeek - mui.repPerWeek as lastWeekDifference
FROM     MoreUserInfo mui
  LEFT JOIN     WeeklyPostSum wps
  ON       mui.id = wps.owneruserid
WHERE    reputation > 500
AND      days > 30
AND      repPerDay > 10
ORDER BY repPerWeek DESC

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results