USE commonswiki_p;

SELECT

 user_name,

 REPLACE

 (REPLACE

 (REPLACE

 (REPLACE

 (REPLACE

 (REPLACE

 (REPLACE

 (REPLACE

 (REPLACE

 (REPLACE (ugu.q1, '0', ''),

 '1', ''),

 '2', ''),

 '3', ''),

 '4', ''),

 '5', ''),

 '6', ''),

 '7', ''),

 '8', ''),

 '9', '') AS taaction,

 sysop.isadmin AS admin

FROM user_groups

INNER JOIN user

ON ug_user=user_id

LEFT JOIN

  (

   SELECT DISTINCT

   log_user AS q1

   FROM logging

   WHERE log_type = "pagetranslation"

   AND log_timestamp > DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -365 DAY), '%Y%m%d%H%i%s')

   ) AS ugu

ON q1 = ug_user

LEFT JOIN

  (

   SELECT

   ug_user AS ugsysop,

   ug_group AS isadmin

   FROM user_groups

   WHERE ug_group = "sysop"

   ) AS sysop

ON ugsysop = ug_user

WHERE ug_group = "translationadmin";