Untitled query #83207

SQL

WITH USER_TYPE AS (  
  select * , 
  case 
      WHEN actor_user is null then 'IP'
      WHEN actor_user is not null and ug_group = 'bot' then 'Bot'
  WHEN actor_user is not null and (ug_group != 'bot' or ug_group is null ) then 'User'
  
      ELSE 'misc'
      END as usertype
  from revision  
  left join actor on actor.actor_id = revision.rev_actor
  left join user on user.user_id = actor.actor_user 
  left join user_groups on user_groups.ug_user = user.user_id 
	
)

select MONTH(rev_timestamp),YEAR(rev_timestamp),usertype,COUNT(*) from USER_TYPE GROUP BY usertype , MONTH(rev_timestamp) , YEAR(rev_timestamp)

LIMIT 10
SQL

Checking query status...
