Question score versus answer score

0

Please login or register to vote for this query.

(click on this box to dismiss)

For a given difference (answer score minus question score), shows how many questions have that difference and the average number of answers per question.

Computational Science

Q&A for scientists using computers to solve scientific problems

-- Question score versus answer score
-- For a given difference (answer score minus question score), shows how many questions have that difference and the average number of answers per question.

DECLARE @DiffBrackets TABLE (
    lowBound        INT,
    highBound       INT,
    numQuestions    INT,
    avgNumAnswers   FLOAT
)
INSERT INTO
    @DiffBrackets (lowBound, highBound)
VALUES
    (-2147483647, -201),
    ( -200,       -101),
    ( -100,        -51),
    (  -50,        -21),
    (  -20,        -11),
    (  -10,         -6),
    (   -5,         -2),
    (   -1,         -1),
    (    0,          0),
    (    1,          1),
    (    2,          5),
    (    6,         10),
    (   11,         20),
    (   21,         50),
    (   51,        100),
    (  101,        200),
    (  201, 2147483647);

UPDATE      @DiffBrackets
SET         Diff.numQuestions   = qc.numQuestions
          , Diff.avgNumAnswers  = qc.avgNumAnswers
FROM        @DiffBrackets   Diff
INNER JOIN  (
    SELECT      COUNT (qSum.Id)             AS numQuestions
              , AVG (CAST (qSum.[Num answers] AS FLOAT) )    AS avgNumAnswers
              , Diff2.lowBound
    FROM        @DiffBrackets   Diff2
    INNER JOIN (
        SELECT      Q.Id
                  , COALESCE (aSum.[Ans score], 0) - Q.Score  AS [scoreDiff]
                  , COALESCE (aSum.[Num answers], 0)  AS [Num answers]
        FROM        Posts Q
        LEFT JOIN   (
                SELECT 
                            A.ParentId
                          , MAX (A.Score)   AS [Ans score]
                          , COUNT (A.Score) AS [Num answers]
                FROM        Posts A
                WHERE       A.PostTypeId    = 2 -- Answers
                GROUP BY    A.ParentId
        ) AS aSum 
        ON          Q.Id                    = aSum.ParentId
        WHERE       Q.PostTypeId            = 1 -- Questions

    ) AS qSum 
    ON          qSum.scoreDiff  BETWEEN Diff2.lowBound  AND Diff2.highBound    
    GROUP BY    Diff2.lowBound

) AS qc
ON              Diff.lowBound   = qc.lowBound;

select * from @DiffBrackets

Enter Parameters

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