Q&A for programmers
--Founders are users that were issued the Generalist badge
--when it was introduced
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 ,
WHEN gs.postcount = 0 THEN NULL
END as postcount,
WHEN gs.postcount = 0 THEN 'Founder'
WHEN MIN = 1 THEN 'Least Effort'
WHEN MAX = 1 THEN 'Most Effort'
FROM generaliststats gs
AVG(postcount) as postcount,
cte.userid [User Link],
LEFT JOIN users u
ON cte.userid = u.id
CASE WHEN cte.PostCount is null then 1 ELSE 0 END,
Error: looks like you typed in the wrong words - try again