Q&A about the site for expert genealogists and people interested in genealogy or family history
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(DISTINCT UP.OwnerUserId) AS UserCount FROM Users U LEFT JOIN UserPostCounts UP ON U.Id = UP.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;