0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional robotic engineers, hobbyists, researchers and students

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 Kategorie,
        COUNT(*) AS "Počet uživatelů"
    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 
    Kategorie,
    "Počet uživatelů",
    ROUND(100.0 * "Počet uživatelů" / (SELECT COUNT(*) FROM Users), 2) AS Procenta
FROM CategoryCounts
ORDER BY
    CASE 
        WHEN Kategorie = '0' THEN 1
        WHEN Kategorie = '1-10' THEN 2
        WHEN Kategorie = '>10' THEN 3
    END;

fork query

edited aug 10

created aug 10 anonymous