0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for members of the workforce navigating the professional setting

WITH
    Answers AS (
        SELECT *
            FROM Posts
            WHERE PostTypeId = 2 -- Answers
    )
SELECT
    Questions.Id,
    DATEDIFF(ss, Questions.CreationDate, FirstAnswer.CreationDate) as SecondsToFirstAnswer,
    DATEDIFF(ss, Questions.CreationDate, AcceptedAnswer.CreationDate) as SecondsToAcceptedAnswer,
    Questions.CreationDate,
    Questions.Tags,
    replace(
         replace(
            replace(
              replace(Questions.Title, ',', '&#44'),
              char(10) + char(13), '<br />'), 
            char(10) , '<br />'),
         char(13) , '<br />') as Title,
    replace(
         replace(
            replace(
              replace(Questions.Body, ',', '&#44'),
              char(10) + char(13), '<br />'), 
            char(10) , '<br />'),
         char(13) , '<br />') as Body
  FROM (
      SELECT TOP ##NumberOfQuestions:int?200##
          Id,
          Title,
          Body,
          Tags,
          CreationDate,
          AcceptedAnswerId,
          AnswerCount
        FROM Posts
        WHERE 
          PostTypeId = 1
          --AND AnswerCount > 0
          and (Tags like '%java%') and (Tags like '%android%') and (Tags not like '%performance%') 
        ORDER BY NEWID()
    ) as Questions
      LEFT OUTER JOIN Answers AS FirstAnswer
        ON FirstAnswer.ParentId = Questions.Id
      LEFT OUTER JOIN Answers AS AcceptedAnswer
        ON AcceptedAnswer.Id = Questions.AcceptedAnswerId
  WHERE
       FirstAnswer.Id = (
            SELECT TOP 1 Id
                FROM Answers
                WHERE
                    Answers.ParentId = Questions.Id
                ORDER BY CreationDate ASC
        )

fork query

edited aug 10 22 anonymous

created apr 9 20 Jacob Bartel