0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Ubuntu users and developers

SELECT 
    a.Id AS AnswerId,
    a.ParentId,
    a.Score AS AnswerScore,
    a.Body AS AnswerBody,
    a.CreationDate AS AnswerCreationDate,
    q.Year,
    SUM(CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE 0 END) AS TotalUpvotes,
    SUM(CASE WHEN v.VoteTypeId = 3 THEN 1 ELSE 0 END) AS TotalDownvotes
FROM 
    (SELECT 
         a.Id,
         a.ParentId,
         a.Score,
         a.Body,
         a.CreationDate,
         YEAR(v.CreationDate) AS Year
     FROM 
         Votes v
     JOIN 
         Posts a ON v.PostId = a.Id AND a.PostTypeId = 2  -- Join votes with answers only
     JOIN 
         Posts q ON a.ParentId = q.Id
     JOIN 
         PostTags pt ON q.Id = pt.PostId
     JOIN 
         Tags t ON pt.TagId = t.Id
     WHERE 
         YEAR(v.CreationDate) BETWEEN 2009 AND 2023
         AND (t.TagName LIKE '%youtube-api%' OR t.TagName LIKE '%youtube-data-api%')
     GROUP BY
         a.Id, a.ParentId, a.Score, a.Body, a.CreationDate, YEAR(v.CreationDate)) AS q
JOIN 
    Posts a ON q.Id = a.Id
JOIN 
    Votes v ON a.Id = v.PostId  -- Join with votes on answers
GROUP BY
    a.Id, a.ParentId, a.Score, a.Body, a.CreationDate, q.Year
ORDER BY
    q.Year, a.Id;

fork query

edited mar 21 anonymous

created mar 21 jon.doe34463