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 
    t.TagName as Platform,
    DATEPART(YEAR, p.CreationDate) as Year,
    COUNT(DISTINCT p.Id) as Posts,
    AVG(CAST(p.Score as FLOAT)) as AvgScore,
    SUM(p.ViewCount) as TotalViews,
    COUNT(DISTINCT CASE WHEN p.AcceptedAnswerId IS NOT NULL THEN p.Id END) as ResolvedIssues,
    COUNT(DISTINCT ph.Id) as ContentChanges
FROM Posts p
LEFT JOIN PostTags pt ON p.Id = pt.PostId
LEFT JOIN Tags t ON pt.TagId = t.Id
LEFT JOIN PostHistory ph ON p.Id = ph.PostId
WHERE t.TagName IN ('expedia', 'booking-com', 'airbnb', 'hotels-com', 'tripadvisor')
    AND p.PostTypeId = 1
GROUP BY t.TagName, DATEPART(YEAR, p.CreationDate)
ORDER BY Year DESC, Posts DESC;

fork query

edited dec 7

created dec 7 anonymous