0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for software engineers working on automated testing, continuous delivery, service integration and monitoring, and building SDLC infrastructure

SELECT 
  q.Id AS [Question ID], 
  q.Title AS [Question Title], 
  q.Body AS [Question Body], 
  a.Id AS [Answer ID], 
  a.Body AS [Answer Body], 
  qc.Text AS [Question Comment], 
  ac.Text AS [Answer Comment], 
  CASE 
    WHEN qc.PostId IS NOT NULL THEN 'Question' 
    ELSE 'Answer' 
  END AS [Comment Type],  -- Determine if comment is for a question or answer
  t.TagName AS [Tag],
  q.CreationDate AS [Question Date], 
  a.CreationDate AS [Answer Date],
  a.Score AS [Answer Score]
FROM 
  Posts q
  JOIN PostTags pt ON q.Id = pt.PostId
  JOIN Tags t ON pt.TagId = t.Id
  LEFT JOIN Posts a ON q.Id = a.ParentId -- Join answers with their parent questions
  LEFT JOIN Comments qc ON q.Id = qc.PostId -- Join comments on questions
  LEFT JOIN Comments ac ON a.Id = ac.PostId -- Join comments on answers
WHERE 
  (t.TagName LIKE '%youtube-api%' OR t.TagName LIKE '%youtube-data-api%') 
  AND a.PostTypeId = 2 -- PostTypeId = 2 refers to answers
  AND a.CreationDate <= '2023-12-31' -- Limit answers to those created before the end of 2023
  AND q.Id BETWEEN 412467 AND 77220623 -- Limit the question ID to the specific range
ORDER BY 
  q.CreationDate DESC

fork query

edited sep 13

created sep 13 jon.doe34463