0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for music historians, critics, and fans

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], 
  ac.Text AS [Answer Comment],  -- Focusing on answer comments
  u.DisplayName AS [Comment Author],  -- Fetching comment author information

  -- Aggregating votes by VoteTypeId
  (SELECT COUNT(*) FROM Votes v WHERE v.PostId = a.Id AND v.VoteTypeId = 3) AS Downvotes

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 ac ON a.Id = ac.PostId -- Join comments on answers only
  LEFT JOIN Users u ON ac.UserId = u.Id  -- Join users who wrote the comments
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 a.Id = 42365034 ;

fork query

edited sep 18 anonymous

created sep 18 jon.doe34463