0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for linguists, teachers and students of the Korean language

-- category table
WITH DIM_Category AS (
  SELECT 1 AS ID, '0' AS Name
  UNION
  SELECT 2, '1-10'
  UNION
  SELECT 3, '>10' 
  UNION 
  SELECT -1, 'N/A'
)

--assign category to users
,Users_cat AS (
    SELECT [u].[id]
           ,CASE 
               WHEN COUNT([p].[id]) = 0 
               THEN 1
               WHEN COUNT([p].[id]) > 0 AND COUNT([p].[id]) <= 10 
               THEN 2
               WHEN COUNT([p].[id]) > 10  
               THEN 3
               ELSE -1
            END AS [cat_id]
      FROM [Users] AS [u]
 LEFT JOIN [Posts] AS [p] 
        ON [p].[OwnerUserId] = [u].[id]
     GROUP BY [u].[id]
)     

-- count number of users in each category
,Users_cat_cnt AS (
     SELECT [c].[ID]
           ,[c].[Name]
           ,COUNT(*) AS cnt_cat
       FROM [DIM_Category] AS c
       JOIN [Users_cat] AS u
         ON c.[id] = u.[cat_id] 
      GROUP BY [c].[ID], [c].[Name]     
      UNION      
     SELECT [c].[ID]
           ,[c].[Name]
           ,0 AS cnt_cat
       FROM [DIM_Category] AS c
  LEFT JOIN [Users_cat] AS u
         ON c.[id] = u.[cat_id] 
      WHERE [u].ID is null
)

-- calculate result
SELECT [c].[ID]
      ,[c].[Name]
      ,CASE
          WHEN [tu].[cnt_all_usr] = 0
          THEN 0
          ELSE ROUND(100 * CAST([c].[cnt_cat] AS float) / [tu].[cnt_all_usr],2)
        END AS pct          
  FROM [Users_cat_cnt] AS c
 CROSS JOIN (
              SELECT count(*) AS cnt_all_usr 
                FROM [Users_cat]
           ) as tu  
 WHERE [c].[ID] > 0           
ORDER BY [c].[ID]

view current version

edited mar 2

created mar 2 jon.doe43923