use wikidatawiki_p;

SELECT

  actor_name AS 'User',

  COUNT(*) AS 'Properties created',

  CASE

    WHEN EXISTS (

      SELECT 1

      FROM user_groups

      WHERE ug_user = actor_user

      AND ug_group = 'bureaucrat'

    ) THEN 'Current bureaucrat'

    WHEN EXISTS (

      SELECT 1

      FROM user_groups

      WHERE ug_user = actor_user

      AND ug_group = 'sysop'

    ) THEN 'Current administrator'

    WHEN EXISTS (

      SELECT 1

      FROM user_groups

      WHERE ug_user = actor_user

      AND ug_group = 'propertycreator'

    ) THEN 'Current property creator'

    WHEN EXISTS (

      SELECT 1

      FROM user_former_groups

      WHERE ufg_user = actor_user

      AND ufg_group = 'propertycreator'

    ) THEN 'Former property creator'

  END AS 'Group'

FROM

  revision_userindex

  JOIN page ON rev_page = page_id

  JOIN actor ON rev_actor = actor_id

WHERE

  page_namespace = 120

  AND rev_parent_id = 0

  #AND rev_timestamp > (DATE_FORMAT((NOW() - INTERVAL 7 DAY),'%Y%m%d%H%i%S'))

  #AND rev_timestamp > (DATE_FORMAT((NOW() - INTERVAL 30 DAY),'%Y%m%d%H%i%S'))

  #AND rev_timestamp > (DATE_FORMAT((NOW() - INTERVAL 365 DAY),'%Y%m%d%H%i%S'))

  AND rev_timestamp > 20230905000000

GROUP BY

  rev_actor

ORDER BY count(*) DESC