Absolute question-able legend

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

SELECT TOP 1
    calculatedData.UserId,
    calculatedData.UserDisplayName,
    calculatedData.UserReputation, 
    calculatedData.UserLastAccessDate,
    calculatedData.QuestionsCount,
    calculatedData.AverageQuestionScore,
    calculatedData.AnswersCount,
    calculatedData.AverageAnswerScore,
    calculatedData.AnswerToQuestionRatio,
    calculatedData.TicketsGenerated,
    calculatedData.TicketsOutstanding
FROM (
    SELECT
        aggregates.UserId,
        aggregates.UserDisplayName,
        aggregates.UserReputation, 
        aggregates.UserLastAccessDate,
        aggregates.QuestionsCount,
        aggregates.AverageQuestionScore,
        aggregates.AnswersCount,
        aggregates.AverageAnswerScore,
        aggregates.AnswerToQuestionRatio,
        CAST(((aggregates.AverageQuestionScore * aggregates.QuestionsCount) / 2) AS DECIMAL(10, 2)) as 'TicketsGenerated',
        CAST((((aggregates.AverageQuestionScore * aggregates.QuestionsCount) / 2) - (aggregates.QuestionsCount - 10)) AS DECIMAL(10, 2)) as 'TicketsOutstanding'
    FROM (
        SELECT 
            users.Id as 'UserId', 
            users.DisplayName as 'UserDisplayName', 
            users.Reputation as 'UserReputation', 
            users.LastAccessDate as 'UserLastAccessDate',
            questions.QuestionsCount,
            CAST(questions.AverageQuestionScore AS DECIMAL(10,2)) as 'AverageQuestionScore', 
            answers.AnswersCount, 
            CAST(answers.AverageAnswerScore AS DECIMAL(10,2)) as 'AverageAnswerScore', 
            CAST((answers.AnswersCount / CAST(questions.QuestionsCount AS DECIMAL(10,2))) AS DECIMAL(10,2)) as 'AnswerToQuestionRatio'

        FROM Users users
            JOIN (
                SELECT 
                    u.Id as "UserId", 
                    COUNT(*) as "QuestionsCount", 
                    AVG(CAST(Score AS DECIMAL(10, 2))) as "AverageQuestionScore"
                FROM Posts p
                    JOIN USERS u
                        ON p.OwnerUserId = u.Id
                WHERE p.PostTypeId = 1
                GROUP BY u.Id
            ) questions
          
                ON users.Id = questions.UserId

            JOIN (
                SELECT 
                    u.Id as "UserId", 
                    COUNT(*) as "AnswersCount", 
                    AVG(CAST(Score AS DECIMAL(10, 2))) as "AverageAnswerScore"
                FROM Posts p
                    JOIN USERS u
                        ON p.OwnerUserId = u.Id
                WHERE p.PostTypeId = 2
                GROUP BY u.Id
            ) answers
          
                ON users.Id = answers.UserId
        WHERE 
            questions.QuestionsCount > 10 AND 
            answers.AnswersCount = 1
        ) aggregates  
    ) calculatedData
WHERE calculatedData.TicketsOutstanding < 0
ORDER BY calculatedData.TicketsOutstanding ASC

Enter Parameters

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