0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for information security professionals

SELECT TOP 15 p.OwnerUserId, COUNT(*) AS post_count
FROM Posts p
GROUP BY p.OwnerUserId
ORDER BY COUNT(*) DESC;	

SELECT TOP 10 u.Id, u.DisplayName, COUNT(*) AS activity_count
FROM (
    SELECT OwnerUserId
    FROM Posts
    WHERE CreationDate >= DATEADD(year, -5, GETDATE())
    UNION ALL
    SELECT UserId
    FROM Comments
    WHERE CreationDate >= DATEADD(year, -5, GETDATE())
) AS p
JOIN Users u ON u.Id = p.OwnerUserId
GROUP BY u.Id, u.DisplayName
ORDER BY COUNT(*) DESC;

SELECT u.Id, u.DisplayName, COUNT(DISTINCT c.PostId) AS unique_posts_commented
FROM Users u
JOIN Comments c ON u.Id = c.UserId
JOIN Posts p ON c.PostId = p.Id
WHERE u.CreationDate <= DATEADD(month, -6, GETDATE())
GROUP BY u.Id, u.DisplayName
HAVING COUNT(DISTINCT c.PostId) >= 10
ORDER BY COUNT(DISTINCT c.PostId) DESC;

SELECT
  COUNT(*) AS TotalPosts,
  SUM(CASE WHEN CommentCount = 0 THEN 1 ELSE 0 END) AS PostsWithNoComments,
  100.0 * SUM(CASE WHEN CommentCount = 0 THEN 1 ELSE 0 END) / COUNT(*) AS PercentageWithNoComments
FROM Posts
WHERE PostTypeId = 1;

fork query

edited jun 5

created jun 5 anonymous