Question, Answer and Votes joined together

0

Please login or register to vote for this query.

(click on this box to dismiss)

Tridion

Q&A for Tridion developers and administrators

SELECT
      Question,
      QuestionDate,
      ViewCount,
      AnswerDate,
      AnswerId,
      AnswerScore,
      AnswerNum,
      Link,
      Tags,
      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'
      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)
          ORDER BY QuestionDate, AnswerDate, VoteDate

Enter Parameters

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