0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professionals and students of engineering

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();

fork query

created dec 2 anonymous