0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for active researchers, academics and students of physics

WITH TotalUsers AS (
    SELECT COUNT(*) AS total_users FROM Users
),
UserPostCounts AS (
    SELECT u.Id, COUNT(p.Id) AS post_count
    FROM Users u
    LEFT JOIN Posts p ON u.Id = p.OwnerUserId
    GROUP BY u.Id
)

SELECT 
    '0 posts' AS category,
    ROUND(100.0 * SUM(CASE WHEN post_count = 0 THEN 1 ELSE 0 END) / (SELECT total_users FROM TotalUsers), 2) AS percentage
FROM UserPostCounts

UNION ALL

SELECT 
    '1-10 posts' AS category,
    ROUND(100.0 * SUM(CASE WHEN post_count BETWEEN 1 AND 10 THEN 1 ELSE 0 END) / (SELECT total_users FROM TotalUsers), 2) AS percentage
FROM UserPostCounts

UNION ALL

SELECT 
    '>10 posts' AS category,
    ROUND(100.0 * SUM(CASE WHEN post_count > 10 THEN 1 ELSE 0 END) / (SELECT total_users FROM TotalUsers), 2) AS percentage
FROM UserPostCounts;

fork query

edited oct 13

created oct 13 anonymous