nlwiki Top patrollers all time sorted by ipMarkeringen

SQL

# https://www.mediawiki.org/wiki/Manual:Logging_table#log_params

WITH raw_list as(
  SELECT
  loguser.actor_name as loguser,
  edituser.actor_name as edituser,
  edituser.actor_user as edituser_actor,
  log_params,
  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, r.rev_timestamp, r.rev_id,
  CASE WHEN	edituser.actor_user IS NULL AND (edituser.actor_name LIKE '%:' OR edituser.actor_name LIKE '%.%') THEN 1 ELSE 0 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 (log_timestamp LIKE '201%' OR log_timestamp LIKE '202%')
  ORDER BY log_timestamp DESC # De meest recente bovenaan
)

#SELECT loguser, log_title, log_params FROM raw_list LIMIT 100

# Stats
WITH stats2 AS (
  SELECT ROW_NUMBER() OVER (ORDER BY ipMarkeringen DESC) AS row_num, raw_list.loguser,
  sum(isAnoniem) as ipMarkeringen,
  sum(NOT isAnoniem) as gebruikerMarkeringen,
  COUNT(*) as Totaal
  FROM raw_list
  GROUP BY raw_list.loguser
  ORDER BY ipMarkeringen DESC
)
SELECT row_num, loguser as Gebruiker, ipMarkeringen as "IP-markeringen", gebruikerMarkeringen as "Gebruiker-markeringen", Totaal
FROM stats2
WHERE Totaal >= 50

# Stats as wikitable
/*SELECT CONCAT('{{intern|1=title=Gebruiker:', REPLACE(raw_list.loguser, ' ', '_'), '|2=', raw_list.loguser, '}}') as Gebruiker,
sum(isAnoniem) as 'IP-markeringen',
sum(NOT isAnoniem) as 'Gebruiker-markeringen',
COUNT(*) as Totaal
FROM raw_list
GROUP BY raw_list.loguser
HAVING COUNT(*) >= 50
ORDER BY sum(isAnoniem) DESC*/
SQL

Checking query status...
