Replicas browser and optimizer
This query is marked as a draft
This query has been published
This query sucks. But it works! (Or, does it?) Several notes: - renamed users don't appear cleanly, or have duplicate entries, or user_registration is greather than log_timestamp - this should use actor - old users don't have user_registration (see e.g. T24097). For them we use the timestamp of the first edit, just like fixUserRegistration would do; to avoid a full table scan, the revision query is limited on rev_id using a hardcoded value - old rights log entries don't have params for added/removed groups... These are circumvented with a check on the summary. - in old entries, the target (log_title) may start with a lowercase letter  or be 'User:User:username' . This also breaks log entries on-wiki... : https://it.wikipedia.org/w/index.php?title=Speciale:Registri&offset=20060323000000&limit=1&type=rights&user=Gac&page=&wpdate=2006-03-22&tagfilter=&day=22&month=3&year=2006 : https://it.wikipedia.org/wiki/Speciale:Registri?type=&user=&page=Utente%3AUtente%3AOrbiliusMagister&wpdate=&tagfilter=
use itwiki_p; select user_name, @us_reg := COALESCE( user_registration, ( SELECT MIN(rev_timestamp) FROM revision WHERE rev_id < 2450000 AND rev_user = user_id ) ) as registration, @sys_tim := log.log_timestamp as sysop_timestamp, DATEDIFF( @sys_tim, @us_reg ) as days from user right -- this should be a simple join, but a right join ensures that data is displayed even for users without registration join ( select log_timestamp, replace( replace( CONCAT( UCASE( CAST( LEFT( log_title, 1) as CHAR ) ), SUBSTRING( log_title, 2 ) ),'_', ' '), 'Utente:', '' ) as name from logging where log_type = 'rights' and ( ( cast(log_params as char) rlike 'newgroups.+sysop' and (char_length(cast(log_params as char))-char_length(replace(cast(log_params as char), 'sysop','')) = char_length('sysop')) ) or cast(log_params as char) rlike '[^\n]*\n.*sysop' or ( log_timestamp < '20060613000000' and (select cast(comment_text as char) as summary from comment where comment_id = log_comment_id) rlike '[sS]ysop' ) ) group by name order by log_id ) as log on log.name = user.user_name order by days
By running queries you agree to the
and you irrevocably agree to release your SQL under
All SQL code is licensed under
Checking query status...
Report a bug!
Wikimedia Cloud VPS
, written in