0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for students, teachers, and linguists wanting to discuss the finer points of the Italian language

WITH UserPostCounts AS (
    SELECT U.Id AS UserId, COUNT(P.Id) AS PostCount
    FROM Users U
    LEFT JOIN Posts P ON U.Id = P.OwnerUserId
    GROUP BY U.Id
),
CategoryCounts AS (
    SELECT
        CASE
            WHEN PostCount = 0 THEN '0'
            WHEN PostCount BETWEEN 1 AND 10 THEN '1-10'
            WHEN PostCount > 10 THEN '>10'
        END AS Category,
        COUNT(*) AS UserCount
    FROM UserPostCounts
    GROUP BY
        CASE
            WHEN PostCount = 0 THEN '0'
            WHEN PostCount BETWEEN 1 AND 10 THEN '1-10'
            WHEN PostCount > 10 THEN '>10'
        END
)
SELECT 
    Category,
    UserCount,
    ROUND(100.0 * UserCount / (SELECT COUNT(*) FROM Users), 2) AS Percentage
FROM CategoryCounts
ORDER BY
    CASE 
        WHEN Category = '0' THEN 1
        WHEN Category = '1-10' THEN 2
        WHEN Category = '>10' THEN 3
    END;

fork query

edited aug 10

created aug 10 anonymous