0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for aircraft pilots, mechanics, and enthusiasts

SELECT
    /*base.Question,*/
    MIN(base.QuestionDate) AS 'Week',
    /*base.ViewCount,
    base.AnswerDate,
    base.AnswerId,
    base.AnswerScore,*/
    AVG(CASE
        WHEN AnswerId IS NULL THEN 0.0
        ELSE 1.0
      END) as PercentageAnswered
  FROM
  (
    SELECT
      Questions.Id as 'Question',
      Questions.CreationDate as 'QuestionDate',
      Questions.ViewCount as 'ViewCount',
      Answers.CreationDate as 'AnswerDate',
      Answers.Id as 'AnswerId',
      Answers.Score as 'AnswerScore',
      ROW_NUMBER() OVER (PARTITION BY Questions.Id ORDER BY Answers.Score DESC) as 'AnswerNum',
      CONCAT('https://stackoverflow.com/questions/', Questions.Id) as 'Link',
      Questions.Tags as 'Tags'
    FROM
      Posts as Questions
      LEFT OUTER JOIN Posts as Answers ON Answers.ParentId = Questions.Id
    WHERE Questions.Tags LIKE '%google-cloud-dataflow%'
  ) AS base
  WHERE base.AnswerNum = 1
  GROUP BY DATEPART(year, base.QuestionDate), DATEPART(month, base.QuestionDate)
  ORDER BY Week

fork query

edited aug 11 20

created aug 11 20 Pablo.Estrada