nlwiki Top patrollers all time sorted by ipMarkeringen

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

SQL

use nlwiki_p;

WITH raw_list as(
  SELECT
  loguser.actor_name as loguser,
  edituser.actor_name as edituser,
  edituser.actor_user as edituser_actor,
  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 (edituser.actor_user IS NULL AND (edituser.actor_name LIKE '%:' OR edituser.actor_name LIKE '%.%')) #anonieme bewerkingen
  #AND (log_timestamp LIKE '201%' OR log_timestamp LIKE '202%')
  ORDER BY log_timestamp DESC #De meest recente bovenaan
  LIMIT 10000
)

#Stats no 1
/*SELECT raw_list.loguser, edituser, edituser_actor, isAnoniem, timestamp_human
FROM raw_list
WHERE edituser LIKE '%.%'
#GROUP BY raw_list.loguser
#ORDER BY COUNT(1) DESC
LIMIT 100*/

# Some stats
SELECT ROW_NUMBER() OVER (ORDER BY ipMarkeringen DESC) AS row_num, raw_list.loguser,
#sum(isAnoniem) as ipMarkeringen1,
sum(case when isAnoniem = 1 then 1 else 0 end) as ipMarkeringen,
sum(case when isAnoniem = 0 then 1 else 0 end) as gebruikerMarkeringen,
COUNT(*) as aantalMarkeringen
FROM raw_list
GROUP BY raw_list.loguser
ORDER BY ipMarkeringen DESC

UNION ALL

SELECT ROW_NUMBER() OVER (ORDER BY ipMarkeringen DESC) AS row_num, raw_list.loguser,
#sum(isAnoniem) as ipMarkeringen1,
sum(case when isAnoniem = 1 then 1 else 0 end) as ipMarkeringen,
sum(case when isAnoniem = 0 then 1 else 0 end) as gebruikerMarkeringen,
COUNT(*) as aantalMarkeringen
FROM raw_list
GROUP BY raw_list.loguser
ORDER BY gebruikerMarkeringen 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...