0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- Percent Answered by Asker Reputation

DECLARE @UserBrackets TABLE (
    userClass   VARCHAR(60),
    lowRep      INT,
    highRep     INT,
    numUsers    INT
)
INSERT INTO
    @UserBrackets (userClass, lowRep, highRep)
VALUES
    ('N00b (or banned)',     1,     1),
    ('Bobcat',               2,     5),
    ('Wolf Cub',             6,    10),
    ('Bear Cub',            11,    15),
    ('Webelo',              16,    50),
    ('Scout',               51,   125),
    ('Tenderfoot',         126,   250),
    ('Second Class',       251,   500),
    ('First Class',        501,  1000),
    ('Star',              1001,  1500),
    ('Life',              1501,  2000),
    ('Eagle',             2001,  3000),
    ('Brownie',           3001, 10000),
    ('Girl Scout',       10001, 20000),
    ('Get a Life!',      20001, 2147483647);

UPDATE      @UserBrackets
SET         rep.numUsers    = uc.userCnt
FROM        @UserBrackets   rep
INNER JOIN  (
    SELECT      COUNT (u.id)    AS userCnt,
                rep.lowRep
    FROM        @UserBrackets   rep
    INNER JOIN  Users           u
    ON          u.Reputation    BETWEEN  rep.lowRep  AND rep.highRep
    GROUP BY    rep.lowRep
) AS uc
ON              rep.lowRep      = uc.lowRep;


WITH questsByRep AS (
    SELECT
        qst.id,
        rep.lowRep,
        CASE
            WHEN qst.acceptedanswerid IS NULL
            THEN 1
            ELSE 0
        END AS Unanswered,
        CASE
            WHEN qst.acceptedanswerid IS NULL
            THEN 0
            ELSE 1
        END AS Answered,

        COALESCE (ans.Score, 0) AS AcceptAnsScore

    FROM        Posts                   qst
    INNER JOIN  Users                   u
    ON          qst.owneruserid         = u.id
    INNER JOIN  @UserBrackets           rep
    ON          u.Reputation            BETWEEN  rep.lowRep  AND rep.highRep
    LEFT JOIN   Posts                   ans
    ON          qst.acceptedanswerid    = ans.id
    WHERE       qst.posttypeid          = 1 -- Questions
)
SELECT      ub.lowRep                                                   AS [Low Rep],
            ub.highRep                                                  AS [High Rep],
            ub.userClass                                                AS [Rank],
            ub.numUsers                                                 AS [Users],
            COUNT (qbr.id)                                              AS Questions,
            SUM (qbr.Answered)                                          AS Answered,
            ROUND (SUM (qbr.Answered)   * 100.0 / COUNT (qbr.id), 0 )   AS [Percent],
            SUM (qbr.Unanswered)                                        AS Unanswered,
            ROUND (SUM (qbr.Unanswered) * 100.0 / COUNT (qbr.id), 0 )   AS [Percent],
            AVG (qbr.AcceptAnsScore)                                    AS [Avg Accept Ans Score]
FROM        questsByRep     qbr
INNER JOIN  @UserBrackets   ub
ON          qbr.lowRep      = ub.lowRep
GROUP BY    ub.lowRep,
            ub.userClass,
            ub.highRep,
            ub.numUsers
ORDER BY    ub.lowRep

fork query

created aug 12 11 Awesome Poodles