Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
This query is marked as a draft
This query has been published
by
KCVelaga
.
Toggle Highlighting
SQL
-- Parameters: -- lp_num: log params key-value pair (differs from wiki to wiki) WITH patrol_logs AS ( SELECT log_id, log_timestamp AS log_ts, DATE(log_timestamp) AS log_dt, CASE WHEN log_namespace = 0 THEN TRUE ELSE FALSE END AS is_ns0, log_title, log_actor, SUBSTRING_INDEX( -- log_params array structure is varied across various wikis -- we will pass the required parameter dynamically SUBSTRING_INDEX(log_params, '"4::curid";s:8:"', -1), '"', 1 ) AS curr_rev_id FROM logging logs WHERE log_type = 'patrol' AND log_page IS NOT NULL AND DATE(log_timestamp) = CURDATE() - INTERVAL 1 DAY ) SELECT pls.*, rc_id, rc_timestamp AS rc_ts, DATE(rc_timestamp) AS rc_dt, rc_new, (actor_user IS NULL) AS is_anon FROM patrol_logs pls JOIN recentchanges rc ON pls.curr_rev_id = rc.rc_this_oldid AND pls.log_title = rc.rc_title JOIN actor ra ON rc.rc_actor = ra.actor_id WHERE rc_patrolled = 1 AND rc_bot = 0 AND curr_rev_id REGEXP '^[0-9]+$'
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
.
Checking query status...