(broken) Retrieve patrolled edits

This query is marked as a draft This query has been published by Smile4ever.
SQL

use nlwiki_p;

# Some stats, try 2
SELECT raw_list.loguser,
(SELECT COUNT(1) WHERE isAnoniem = TRUE) as anoniemN,
(SELECT COUNT(1) WHERE isAnoniem = FALSE) as gebruikerN,
(SELECT COUNT(1)) as totaalN
FROM
(
  SELECT loguser.actor_name as loguser,
  log_timestamp, CONCAT(SUBSTRING(log_timestamp, 1, 8), ' ', SUBSTRING(log_timestamp, 9, 2), ':', SUBSTRING(log_timestamp, 11, 2), ':', SUBSTRING(log_timestamp, 13, 2)) as timestamp_human,
  log_title,
  edituser.actor_name as edituser,
  r.rev_timestamp, r.rev_id,
  CASE edituser.actor_user WHEN edituser.actor_user IS NULL AND (edituser.actor_name LIKE '%:' OR edituser.actor_name LIKE '%.%') THEN TRUE ELSE FALSE END as isAnoniem
  FROM logging l
  INNER JOIN revision r ON SUBSTRING(l.log_params, 26, 8) = r.rev_id # edit_id = rev_id
  INNER JOIN actor loguser ON loguser.actor_id = log_actor
  INNER JOIN actor edituser ON edituser.actor_id = rev_actor
  WHERE log_type = 'patrol' #Enkel markeringen
  AND log_namespace = '0' #Enkel hoofdnaamruimte
  AND log_deleted = '0'
  #AND (edituser.actor_user IS NULL AND (edituser.actor_name LIKE '%:' OR edituser.actor_name LIKE '%.%')) #anonieme bewerkingen
  #AND (log_timestamp LIKE '2009%' OR log_timestamp LIKE '201%')
  ORDER BY log_timestamp DESC #De meest recente bovenaan
  LIMIT 10000
) as raw_list
GROUP BY raw_list.loguser
ORDER BY COUNT(1) DESC

# Raw list
# Let's make some stats    
/*SELECT raw_list.loguser, COUNT(1) as aantal
FROM
(
  SELECT loguser.actor_name as loguser, log_timestamp, CONCAT(SUBSTRING(log_timestamp, 1, 8), ' ', SUBSTRING(log_timestamp, 9, 2), ':', SUBSTRING(log_timestamp, 11, 2), ':', SUBSTRING(log_timestamp, 13, 2)) as timestamp_human, log_title, edituser.actor_name as edituser, r.rev_timestamp, r.rev_id
  FROM logging l
  INNER JOIN revision r ON SUBSTRING(l.log_params, 26, 8) = r.rev_id # edit_id = rev_id
  INNER JOIN actor loguser ON loguser.actor_id = log_actor
  INNER JOIN actor edituser ON edituser.actor_id = rev_actor
  WHERE log_type = 'patrol' #Enkel markeringen
  AND log_namespace = '0' #Enkel hoofdnaamruimte
  AND log_deleted = '0'
  #AND (edituser.actor_user IS NULL AND (edituser.actor_name LIKE '%:' OR edituser.actor_name LIKE '%.%')) #anonieme bewerkingen
  #AND (log_timestamp LIKE '2009%' OR log_timestamp LIKE '201%')
  ORDER BY log_timestamp DESC #De meest recente bovenaan
  #LIMIT 10000
) as raw_list
GROUP BY raw_list.loguser
ORDER BY COUNT(1) DESC*/
             
             
             
/*Difference with the one below?
SELECT log_user_text, COUNT(1) as aantal
FROM logging
WHERE log_type = 'patrol' #Enkel markeringen
AND log_namespace = '0' #Enkel hoofdnaamruimte
AND log_deleted = '0'
GROUP BY log_user_text
ORDER BY COUNT(1) DESC*/


/*SELECT a.actor_name, COUNT(1) as aantal
FROM logging l INNER JOIN actor a ON l.log_actor = a.actor_id
WHERE l.log_type = 'patrol' #Enkel markeringen
AND l.log_namespace = '0' #Enkel hoofdnaamruimte
AND l.log_deleted = '0'
GROUP BY a.actor_name
ORDER BY COUNT(1) DESC*/

/*SELECT l.log_title, COUNT(1)
FROM logging l
WHERE l.log_timestamp LIKE '2019%'
AND l.log_type = 'patrol' #Enkel markeringen
AND l.log_namespace = '0' #Enkel hoofdnaamruimte
AND l.log_deleted = '0'
GROUP BY l.log_title
ORDER BY COUNT(1) DESC*/
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
