0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Ubuntu users and developers

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(UPC.OwnerUserId) AS UserCount
    FROM 
        Users U
    LEFT JOIN 
        UserPostCounts UPC ON U.Id = UPC.OwnerUserId
    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,
    COALESCE(uc.UserCount, 0) AS Počet_uživatelů,
    ROUND((COALESCE(uc.UserCount, 0) * 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