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 us.Id, COUNT(po.Id) AS post_count
    FROM Users AS us
    LEFT JOIN Posts AS po ON us.Id = po.OwnerUserId
    GROUP BY us.Id
)

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

UNION ALL

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

UNION ALL

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

fork query

edited oct 13

created oct 13 anonymous