Q&A for developers building blockchains with the Substrate SDK
SELECT TOP 10 Users.Id, Users.DisplayName, PostCounts.PostCount + CommentCounts.CommentCount AS PostsAndComments FROM Users LEFT JOIN ( SELECT OwnerUserId, COUNT(*) AS PostCount FROM Posts WHERE CreationDate >= DATEADD(YEAR, -5, GETDATE()) GROUP BY OwnerUserId ) AS PostCounts ON Users.Id = PostCounts.OwnerUserId LEFT JOIN ( SELECT UserId, COUNT(*) AS CommentCount FROM Comments WHERE CreationDate >= DATEADD(YEAR, -5, GETDATE()) GROUP BY UserId ) AS CommentCounts ON Users.Id = CommentCounts.UserId ORDER BY PostsAndComments DESC;