0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for administrators, end users, developers and designers for Craft CMS

WITH UserPostCounts AS (
    SELECT OwnerUserId, COUNT(*) AS PostCount
    FROM Posts
    GROUP BY OwnerUserId
),
UserCategories AS (
    SELECT
        CASE 
            WHEN PostCount = 0 THEN '0'
            WHEN PostCount BETWEEN 1 AND 10 THEN '1-10'
            ELSE '>10'
        END AS PostCategory,
        COUNT(*) AS UserCount
    FROM UserPostCounts
    GROUP BY
        CASE 
            WHEN PostCount = 0 THEN '0'
            WHEN PostCount BETWEEN 1 AND 10 THEN '1-10'
            ELSE '>10'
        END
),
TotalUsers AS (
    SELECT COUNT(*) AS TotalCount FROM Users
)
SELECT 
    uc.PostCategory AS Kategorie_postu,
    uc.UserCount AS Počet_uživatelů,
    ROUND((uc.UserCount * 100.0) / tu.TotalCount, 2) AS Procento_z_celkoveho_poctu
FROM 
    UserCategories uc
CROSS JOIN 
    TotalUsers tu
ORDER BY
    CASE 
        WHEN uc.PostCategory = '0' THEN 1
        WHEN uc.PostCategory = '1-10' THEN 2
        ELSE 3
    END;

fork query

edited may 27

created may 27 anonymous