0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for serious players and enthusiasts of chess

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
order by kat

view current version

edited sep 20

created sep 20 jon.doe45742