Q&A about the site for power users of web applications
SELECT u.Id AS UserId, COUNT(DISTINCT q.Id) AS Questions, COUNT(DISTINCT a.Id) AS Answers, SUM(CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE 0 END) AS Upvotes, SUM(CASE WHEN v.VoteTypeId = 3 THEN 1 ELSE 0 END) AS Downvotes, SUM(q.ViewCount) AS QuestionsViewed, COUNT(DISTINCT f.PostId) AS Favourites, SUM(u.Views) AS ProfileViews FROM Users u LEFT JOIN Posts q ON u.Id = q.OwnerUserId AND q.PostTypeId = 1 AND q.CreationDate >= '2022-01-01' -- Limiting to posts from 2022 onwards LEFT JOIN Posts a ON u.Id = a.OwnerUserId AND a.PostTypeId = 2 AND a.CreationDate >= '2022-01-01' LEFT JOIN Votes v ON u.Id = v.UserId AND v.CreationDate >= '2022-01-01' LEFT JOIN PostHistory f ON u.Id = f.UserId AND f.PostHistoryTypeId = 5 AND f.CreationDate >= '2022-01-01' WHERE u.Id IN (1) GROUP BY u.Id ORDER BY u.Id;