Top 100 verificatori di itwiki

Query per selezionare gli utenti che più di tutti hanno effettuato verifiche (patrol) su Wikipedia in italiano negli ultimi 24 mesi.

SQL

SELECT
    COUNT(log_id) AS verifiche,
    user_name AS nome_utente,
    user_editcount AS edit_utente,
    GROUP_CONCAT(DISTINCT ug_group SEPARATOR ", ") AS gruppi_dell_utente
FROM logging log
JOIN actor AS act
	ON log.log_actor = act.actor_id
JOIN user us
  ON act.actor_user = us.user_id
JOIN user_groups ug
  ON us.user_id = ug.ug_user
WHERE log.log_type = 'patrol'
AND log.log_action = 'patrol'
AND log.log_timestamp > ( NOW() - INTERVAL 24 MONTH )
AND ( log.log_params LIKE '%"6::auto";i:0%'
OR    log.log_params LIKE '%\n0' /* not autopatrolled */ )
GROUP BY user_name
ORDER BY verifiche DESC
LIMIT 100;

SQL

Checking query status...