0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people looking to bypass life's everyday problems with simple tricks

-- AcceptedAnswers NewUsers vs experinced

WITH acceptedansers 
     AS (SELECT acceptedanswer.id, 
                CASE 
                  WHEN reputation < 15 THEN 1 
                  ELSE 0 
                END AS newuser, 
                CASE 
                  WHEN reputation >= 15 THEN 1 
                  ELSE 0 
                END AS expuser, 
                CASE 
                  WHEN acceptedanswer.score = 0 THEN 'Zero Accepted Answer' 
                  ELSE 'NonZero Accepted Answer' 
                END AS TYPE 
         FROM   posts question 
                INNER JOIN posts acceptedanswer 
                  ON question.acceptedanswerid = acceptedanswer.id 
                INNER JOIN users u 
                  ON question.owneruserid = u.id 
         WHERE  question.posttypeid = 1) 
SELECT COUNT(id)    coundid, 
       SUM(newuser) newuser, 
       SUM(expuser) expuser, 
       TYPE 
FROM   acceptedansers 
GROUP  BY TYPE 

fork query

created apr 26 11 conradfrix