Answer trends per MM/YY

0

Please login or register to vote for this query.

(click on this box to dismiss)

A break down of answer trends per MM/YY

Ukrainian Language Meta

Q&A about the site for linguists, teachers and students of the Ukrainian language

-- Answer trends per MM/YY
-- A break down of answer trends per MM/YY

WITH total_questions(yr, mth, cnt) AS (
   SELECT Year(CreationDate), Month(CreationDate), COUNT(*)
   FROM Posts
   WHERE PostTypeId = 1 AND TAGS LIKE '%asp.net%'
   GROUP BY Year(CreationDate), Month(CreationDate)
),
total_answers(yr, mth, cnt) AS (
   SELECT Year(CreationDate), Month(CreationDate), COUNT(*)
   FROM Posts
   WHERE PostTypeId = 2 AND TAGS LIKE '%asp.net%'
   GROUP BY Year(CreationDate), Month(CreationDate)
),
questions_with_accepted(yr, mth, cnt) AS (
   SELECT Year(CreationDate), Month(CreationDate), COUNT(*)
   FROM Posts
   WHERE PostTypeId = 1 AND TAGS LIKE '%asp.net%'
      AND AcceptedAnswerId IS NOT NULL
   GROUP BY Year(CreationDate), Month(CreationDate)
),
questions_with_answers(yr, mth, cnt) AS (
   SELECT Year(CreationDate), Month(CreationDate), COUNT(*)
   FROM Posts
   WHERE PostTypeId = 1 AND TAGS LIKE '%asp.net%'
      AND AnswerCount > 0
   GROUP BY Year(CreationDate), Month(CreationDate)
),
answer_counts(yr, mth, cnt) AS (
   SELECT Year(CreationDate), Month(CreationDate), ROUND(AVG(CONVERT(DECIMAL(10,5),AnswerCount)), 2)
   FROM Posts
   WHERE PostTypeId = 1
   GROUP BY Year(CreationDate), Month(CreationDate)
)
SELECT tq.yr AS Year, tq.mth AS Month, tq.cnt AS Questions, ta.cnt AS Answers, ac.cnt AS AvergeNoAnswers,
    qwa.cnt AS QuestionsAccepted, ROUND(CONVERT(DECIMAL(10,5),qwa.cnt)/tq.cnt * 100, 2) AS AcceptedRate, 
    qaa.cnt AS QuestionsAnswered, ROUND(CONVERT(DECIMAL(10,5),qaa.cnt)/tq.cnt * 100, 2) AS AnsweredRate
    
FROM total_questions tq
    JOIN total_answers ta ON ta.yr = tq.yr AND ta.mth = tq.mth
    JOIN questions_with_accepted qwa ON qwa.yr = tq.yr AND qwa.mth = tq.mth
    JOIN questions_with_answers qaa ON qaa.yr = tq.yr AND qaa.mth = tq.mth
    JOIN answer_counts ac ON ac.yr = tq.yr AND ac.mth = tq.mth
ORDER BY tq.yr DESC, tq.mth DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results