0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Tridion developers and administrators

SELECT
    /*main.Question,*/
    MIN(main.QuestionDate) as period,
    /*main.ViewCount,
    main.AnswerDate,
    main.AnswerId,
    main.AnswerScore,*/
    COUNT(main.VoteId)/SUM(main.ViewCount/100.0) as VotesPer100Views,
    COUNT(main.VoteId)/SUM(main.AnswerViews/100.0) as VotesPer100Viewses
FROM (
    SELECT
          Question,
          QuestionDate,
          ViewCount,
          AnswerDate,
          AnswerId,
          AnswerScore,
          AnswerNum,
          Link,
          Tags,
          AnswerViews,
          Votes.Id as 'VoteId',
          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',
            Answers.ViewCount as 'AnswerViews'
          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*/
    ) AS main
    WHERE DATEDIFF(day, main.AnswerDate, main.VoteDate) <= 30  /* Votes within 30 days of answer */
          AND AnswerNum = 1  /* The top voted answer */
          GROUP BY DATEPART(month, main.QuestionDate), DATEPART(year, main.QuestionDate)
    ORDER BY period

fork query

edited aug 13

created aug 12 Pablo.Estrada