0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for people who love being outdoors enjoying nature and wilderness, and learning about the required skills and equipment

SELECT
    /*main.Question,*/
    MIN(main.QuestionDate) as period,
    /*main.ViewCount,
    main.AnswerDate,
    main.AnswerId,
    main.AnswerScore,*/
    AVG(DATEDIFF(day, QuestionDate, AnswerDate)) as TimeToFirstAnswer,
    AVG(DATEDIFF(day, AnswerDate, VoteDate)) as TimeToFirstVote,
    AVG(CASE
        WHEN AnswerId IS NULL THEN 0.0
        ELSE 100.0
      END) as PercentageAnswered
FROM (
    SELECT
      Question,
      QuestionDate,
      ViewCount,
      AnswerDate,
      AnswerId,
      AnswerScore,
      AnswerNum,
      Link,
      Tags,
      MIN(Votes.CreationDate) as 'VoteDate'

    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
  LEFT OUTER JOIN Votes as Votes ON AnswerId = Votes.PostId
  WHERE
    (Votes.VoteTypeId IN (1, 2, 5)  /*Vote is accepted, upvoted, favorited*/
    OR AnswerId IS NULL)
  GROUP BY Question, QuestionDate, AnswerDate, AnswerId, ViewCount, AnswerScore, AnswerNum, Link, Tags
    ) AS main
    /*WHERE */
          GROUP BY DATEPART(month, main.QuestionDate), DATEPART(year, main.QuestionDate)
    ORDER BY period

fork query

edited aug 13

created aug 12 Pablo.Estrada