0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people who love being outdoors enjoying nature and wilderness, and learning about the required skills and equipment

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],  -- Only focusing on answer comments
  t.TagName AS [Tag],
  a.Score AS [Answer Score],
  
  -- Calculating upvotes
  (SELECT COUNT(*) FROM Votes v WHERE v.PostId = a.Id AND v.VoteTypeId = 2) AS Upvotes,
  
  -- Calculating downvotes
  (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
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 IN (40710930, 42395303, 44361018, 44417624, 44736690, 45523953, 46132294, 
               47536513, 47556367, 58068828, 49981109, 50085013, 50507296, 52535154, 
               54330350, 55633576, 56224370, 57270828, 57885967, 59625448, 60026544, 
               62644866, 63811627, 69260879, 68357612, 69652231, 70234572, 74108656, 
               74614963, 9756123, 13380190, 12842420, 13589148, 14569880, 15255130, 
               17118366, 19254755, 27206089, 27838490, 27985611, 38926934, 34350973, 
               34769720, 38812378, 36708264, 36997094, 37863046, 39248115, 39902888, 
               40790856, 40842800, 48385765, 43166848, 43349486, 46612037, 46774250, 
               48802886, 49904974, 57912282, 62462560, 63672573, 66959061, 71409194, 
               75194888, 8881910, 9970665, 12385125, 13968471, 20808092, 14169684, 
               14860395, 14983623, 14977250, 19135912, 19732032, 22384747, 26972241, 
               28374165, 31158524, 31261934, 32284685, 34097066, 40172994, 46512057, 
               46960999, 47799043, 48475233, 53253474, 62919851, 67847144, 69259093, 
               71285048, 7570264, 11221787, 9765739, 12157521, 12415238, 16133804, 
               46414532, 17240637, 18435933, 31288731, 26085062, 29620560, 29132359, 
               38727387, 36377708, 39619424, 43765323, 47283007, 48657597, 54352888, 
               57849995, 59298544, 60953148, 62686472, 64628045, 68811728, 3797990, 
               4368404, 6724251, 7653897)
ORDER BY 
  q.CreationDate DESC;

fork query

created sep 16 jon.doe34463