0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for music historians, critics, and fans

SELECT TOP 500 
       u.id AS UserId,
       u.Reputation,
       u.CreationDate AS AcctCreationDate,
       u.LastAccessDate,
       u.Views AS ProfileViews,
       u.downvotes,
       COUNT(DISTINCT p.id) AS TotalPosts, -- Total count of all posts for the user
       COUNT(CASE WHEN p.PostTypeId = 1 THEN 1 END) AS CountPostType1, -- Count where PostTypeId = 1
       COUNT(CASE WHEN p.PostTypeId = 2 THEN 1 END) AS CountPostType2, -- Count where PostTypeId = 2
       COUNT(CASE WHEN p.closeddate IS NOT NULL THEN 1 END) AS CountClosedPosts -- Count of posts with closeddate not null
FROM users u
LEFT JOIN posts p ON u.id = p.owneruserid
GROUP BY u.id, u.Reputation, u.CreationDate, u.LastAccessDate, u.Views, u.downvotes
ORDER BY RAND();

fork query

edited dec 2

created dec 2 anonymous