0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for members of the workforce navigating the professional setting

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;

fork query

created feb 12 anonymous