Fork of Detailed user analytics by Rtnf
This query is marked as a draft This query has been published by Rtnf.

SQL

x
 
set @target = "Silencemen21";
SELECT user_name,
CONCAT(substr(user_registration,1,4),"-",substr(user_registration,5,2), "-",substr(user_registration,7,2)) AS 'sejak',
user_editcount AS 'edit count',  
CONCAT(substr(rev_timestamp,1,4),"-",substr(rev_timestamp,5,2), "-",substr(rev_timestamp,7,2)," ",substr(rev_timestamp,9,2)+7,":",substr(rev_timestamp,11,2)) as 'Last Edit',
CONCAT(substr(log_timestamp,1,4),"-",substr(log_timestamp,5,2), "-",substr(log_timestamp,7,2)," ",mod(substr(log_timestamp,9,2)+7,24),":",substr(log_timestamp,11,2)) as 'Last Log',
GROUP_CONCAT(ug1.ug_group  SEPARATOR ' - ') AS 'role'
FROM `user`
LEFT JOIN user_groups ug1
    ON user_id = ug1.ug_user /*AND ug1.ug_group IN ('editor')*/
JOIN actor
    ON actor_user = user_id
JOIN revision_userindex
    ON rev_id = (SELECT rev_id FROM revision_userindex
                 WHERE actor_id = rev_actor
                 ORDER BY rev_timestamp DESC
                 LIMIT 1)
                 
JOIN logging_userindex
    ON log_id = (SELECT log_id FROM logging_userindex
                 WHERE actor_id = log_actor
                 ORDER BY log_timestamp DESC
                 LIMIT 1)
where user_name = @target
  GROUP BY user_name
  ORDER BY log_timestamp DESC
  LIMIT 100
;
select 
page_title as 'Artikel',
rev_len
from revision_userindex r1  
join actor_revision on actor_id = r1.rev_actor
left join page on page_id=r1.rev_page 
where actor_name  = @target
order by rev_len desc
limit 100;
;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.

Checking query status...