Q&A about the site for students, teachers, and linguists wanting to discuss the finer points of the Italian language
WITH POCET_UZIV AS( --POCET UZIVATELU CELKEM SELECT COUNT(1) AS POCET_UZIV_CELKEM FROM Users WHERE ID BETWEEN 1 AND 10000 ) , UZIV_BEZ_POSTU AS( SELECT count(1) USERS_NO_POST from users u where ID BETWEEN 1 AND 10000 and not exists( SELECT 1 FROM POSTS P WHERE PostTypeId in (1,2) and ID BETWEEN 1 AND 10000 and p.ownerUserId =u.id) ) , POSTY_UZIV AS( --pocet uzivatelu dle kategorie poctu postu SELECT COALESCE(KAT,'POSTY_CELKEM') AS KAT, COUNT(1) AS POCET FROM( select ownerUserId, pocet, case when pocet<=10 then 'Z9' else 'Z11' end as kat from( SELECT p.ownerUserId, count(1) as pocet FROM POSTS P WHERE p.ownerUserId is not null and PostTypeId in (1,2) AND P.ID BETWEEN 1 AND 10000 group by p.ownerUserId )A )B --GROUP BY ROLLUP (KAT) GROUP BY GROUPING SETS(KAT,()) ) , SOUHRN AS( select kat, case when KAT='POSTY_CELKEM' THEN USERS_NO_POST ELSE POCET END AS POCET, POCET_UZIV_CELKEM, USERS_NO_POST FROM( SELECT * FROM POSTY_UZIV CROSS JOIN POCET_UZIV CROSS JOIN UZIV_BEZ_POSTU )A ) SELECT case kat when 'Z11' THEN '>10' when 'Z9' THEN '1-10' ELSE '0' END AS Kategorie, POCET AS "Počet uživatelů", round(1.0*pocet/POCET_UZIV_CELKEM,4)*100 as "% z uzivatelu celkem" FROM SOUHRN