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 1000 
       u.id AS UserId,
       u.Reputation,
       u.CreationDate AS AcctCreationDate,
       u.LastAccessDate,
       u.Views AS ProfileViews,
       u.downvotes,
       COUNT(DISTINCT p.id) AS TotalPosts, 
       COUNT(CASE WHEN p.PostTypeId = 1 THEN 1 END) AS CountQuestion, 
       COUNT(CASE WHEN p.PostTypeId = 2 THEN 1 END) AS CountAnswer, 
       COUNT(CASE WHEN p.closeddate IS NOT NULL THEN 1 END) AS CountClosedPosts
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();

view current version

edited dec 2

created dec 2 anonymous