0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Drupal developers and administrators

SELECT
    /*base.Question,*/
    MIN(base.QuestionDate) AS 'Week',
    /*base.ViewCount,
    base.AnswerDate,
    base.AnswerId,
    base.AnswerScore,*/
    AVG((100.0*base.AnswerScore)/base.ViewCount) AS VotesPer100Views,
    LOG(AVG(base.ViewCount)) as LogViews
  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 AND base.AnswerId IS NOT NULL
  GROUP BY DATEPART(year, base.QuestionDate), DATEPART(week, base.QuestionDate)
  ORDER BY Week

fork query

edited aug 11

created aug 11 Pablo.Estrada