Proportions of questions that go unanswered by Month - Beam / Dataflow

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
  MIN(qa.QuestionDate),
  --SUM(CASE WHEN qa.AnswerCount=0 THEN 1 ELSE 0 END) / 7.0 as 'unanswered questions',
  --SUM(CASE WHEN qa.AnswerCount=0 THEN 0 ELSE 1 END) / 7.0 as 'answered questions',
  SUM(CASE WHEN qa.AnswerCount=0 THEN 1.0 ELSE 0.0 END) / SUM(CASE WHEN qa.AnswerCount=0 THEN 1.0 ELSE 1.0 END) as 'percentage unanswered'
FROM
(
  SELECT
    Questions.Id as 'Question',
    Questions.CreationDate as 'QuestionDate',
    COUNT(Answers.Id) as 'AnswerCount'
  FROM
    Posts as Questions
    LEFT JOIN Posts as Answers ON Answers.ParentId = Questions.Id
  WHERE
  (Questions.Tags LIKE '%apache-beam%' OR Questions.Tags LIKE '%google-cloud-dataflow%')
    GROUP BY Questions.Id, Questions.CreationDate
    --GROUP BY DATEPART(week, Answers.CreationDate), DATEPART(year, Answers.CreationDate)
) AS qa
  GROUP BY DATEPART(month, qa.QuestionDate), DATEPART(year, qa.QuestionDate)
  ORDER BY MIN(qa.QuestionDate);

Enter Parameters

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