0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for programmers

--Founders are users that were issued the Generalist badge 
--when it was introduced

WITH generaliststats 
     AS (SELECT b.userid, 
                SUM(CASE WHEN b.DATE < '2010-05-24' THEN 0 ELSE 1 END) postcount,                 
                RANK() OVER (ORDER BY COUNT(p.id)) MIN, 
                RANK() OVER (ORDER BY SUM(CASE WHEN b.DATE < '2010-05-24' THEN 0 ELSE 1 END) DESC) MAX 
         FROM   badges b 
                INNER JOIN posts p 
                  ON b.userid = p.owneruserid 
                     AND p.posttypeid = 2 
                     AND b.DATE >= p.creationdate 
         WHERE  name = 'Generalist' 
         GROUP  BY b.userid) ,
CTE as (         
SELECT gs.userid , 
       CASE 
         WHEN gs.postcount = 0 THEN NULL 
         ELSE gs.postcount 
       END as postcount, 
       CASE 
         WHEN gs.postcount = 0 THEN 'Founder' 
         WHEN MIN = 1 THEN 'Least Effort' 
         WHEN MAX = 1 THEN 'Most Effort' 
       END DESCRIPTION 
FROM   generaliststats gs 
    
UNION 
SELECT NULL, 
       AVG(postcount) as postcount, 
       'Average Effort' 
FROM   generaliststats 
)
SELECT 
      cte.userid [User Link],
      PostCount,
      Description
FROM 
  CTE 
  LEFT JOIN users u 
         ON cte.userid = u.id 
ORDER BY
  CASE WHEN cte.PostCount is null then 1 ELSE 0 END,
  cte.PostCount,
  u.DisplayName

fork query

edited feb 3 12

created feb 3 12 conradfrix