Citations with redundant publisher links (new schema), recently edited

Fork of Citations with redundant publisher links (new schema) by Nemo bis

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

Pages edited by oabot but not citation bot in the past 3 months. May need further cleanup.

SQL

USE enwiki_p;
SELECT p.page_title, max(r2.rev_timestamp) AS oabot_ts, max(r3.rev_timestamp) AS citbot_ts
FROM page p
JOIN revision r2
ON r2.rev_page = p.page_id
AND p.page_namespace = 0
-- OAbot actor ID
AND r2.rev_actor = 29612637
AND r2.rev_timestamp > DATE(NOW() - INTERVAL 1 WEEK)
AND p.page_id IN (SELECT DISTINCT(el_from)
                FROM externallinks
                WHERE el_to_domain_index LIKE 'http://com.tandfonline.www.'
                OR el_to_domain_index LIKE 'https://com.tandfonline.www.'
                OR el_to_domain_index LIKE 'http://com.sciencedirect.www.'
                OR el_to_domain_index LIKE 'https://com.sciencedirect.www.'
                OR el_to_domain_index LIKE 'http://com.elsevier.linkinghub.'
                OR el_to_domain_index LIKE 'https://com.elsevier.linkinghub.'
                OR el_to_domain_index LIKE 'http://com.elsevierhealth.journals.www.'
                OR el_to_domain_index LIKE 'https://com.elsevierhealth.journals.www.'
                OR el_to_domain_index LIKE 'http://com.springer.link.'
                OR ( el_to_domain_index LIKE 'https://com.springer.link.'
                    AND NOT el_to_path LIKE '/journal/%' )
                OR el_to_domain_index LIKE 'http://com/doi.sagepub.journals.'
                OR el_to_domain_index LIKE 'https://com/doi.sagepub.journals.'
                OR el_to_domain_index LIKE 'http://org/doi.acs.pubs.'
                OR el_to_domain_index LIKE 'https://org/doi.acs.pubs.'
                OR el_to_domain_index LIKE 'http://org.ieee.ieeexplore.'
                OR el_to_domain_index LIKE 'https://org.ieee.ieeexplore.'
                OR ( el_to_domain_index LIKE 'http://com.nature.www.' 
                    AND el_to_path NOT LIKE '/news/%' )
                OR ( el_to_domain_index LIKE 'https://com.nature.www.'
                    AND el_to_path NOT LIKE '/news/%' )
                OR el_to_domain_index LIKE 'http://org.sciencemag.www.'
                OR el_to_domain_index LIKE 'https://org.sciencemag.www.'
                OR el_to_domain_index LIKE 'http://org.sciencemag.science.'
                OR el_to_domain_index LIKE 'https://org.sciencemag.science.'
                OR el_to_domain_index LIKE 'http://com.oup.academic.'
                OR el_to_domain_index LIKE 'https://com.oup.academic.'
                OR el_to_domain_index LIKE 'http://org.cambridge.journals.'
                OR el_to_domain_index LIKE 'https://org.cambridge.journals.'
                OR el_to_domain_index LIKE 'https://org.cambridge.www.'
                OR el_to_domain_index LIKE 'http://org.iop.iopscience.'
                OR el_to_domain_index LIKE 'https://org.iop.iopscience.'
                OR el_to_domain_index LIKE 'http://org.iop.stacks.'
                OR el_to_domain_index LIKE 'http://com.cell.www.'
                OR el_to_domain_index LIKE 'https://com.cell.www.'
                OR el_to_domain_index LIKE 'http://com.thelancet.www.'
                OR el_to_domain_index LIKE 'https://com.thelancet.www.'
                OR el_to_domain_index LIKE 'http://com.bmj.www.'
                OR el_to_domain_index LIKE 'https://com.bmj.www.'
                OR el_to_domain_index LIKE 'http://com.ingentaconnect.www.'
                OR el_to_domain_index LIKE 'https://com.ingentaconnect.www.'
                OR ( el_to_domain_index LIKE 'http://org.acm.dl.'
                    AND el_to_path NOT LIKE '/author_page.cfm%' )
                OR ( el_to_domain_index LIKE 'https://org.acm.dl.'
                    AND el_to_path NOT LIKE '/author_page.cfm%' )
                OR el_to_domain_index LIKE 'http://org.acm.doi.'
                OR el_to_domain_index LIKE 'http://com.wkhealth.meta.'
                OR el_to_domain_index LIKE 'https://com.wkhealth.meta.'
                OR el_to_domain_index LIKE 'http://com.lww.journals.'
                OR el_to_domain_index LIKE 'https://com.lww.journals.'
                OR el_to_domain_index LIKE 'http://org.nejm.www.'
                OR el_to_domain_index LIKE 'https://org.nejm.www.'
                OR el_to_domain_index LIKE 'http://org.apa.psycnet.'
                OR el_to_domain_index LIKE 'https://org.apa.psycnet.'
                OR el_to_domain_index LIKE 'http://org.ama-assn.jama.'
                OR el_to_domain_index LIKE 'http://com.jamanetwork.jama.'
                OR el_to_domain_index LIKE 'https://org.ama-assn.jama.'
                OR el_to_domain_index LIKE 'https://com.jamanetwork.jama.'
                OR el_to_domain_index LIKE 'http://com.informaworld.www.'
                OR el_to_domain_index LIKE 'http://org.royalsocietypublishing.%'
                OR ( ( el_to_domain_index LIKE 'http://com.wiley.onlinelibrary%'
                      OR el_to_domain_index LIKE 'https://com.wiley.onlinelibrary%' )
                   AND NOT el_to_path LIKE '/journal/%' )
                OR el_to_domain_index LIKE 'http://com.wiley.doi%'
                OR el_to_domain_index LIKE 'https://com.els-cdn.%'
                OR el_to_domain_index LIKE 'http://com.serialssolutions.%'
                OR el_to_domain_index LIKE 'https://org.oclc.idm.%'
               )
LEFT JOIN revision r3
ON r3.rev_page = p.page_id
-- Citation bot actor ID
AND r3.rev_actor = 307
AND r3.rev_timestamp > DATE(NOW() - INTERVAL 1 WEEK)
-- Exclude DOI removals identified by the decreased page size
AND r3.rev_id NOT IN ( SELECT r.rev_id
                  FROM revision r
                  JOIN comment c
                  ON r.rev_actor = 29612637
                  AND r.rev_comment_id = c.comment_id
                  AND r.rev_timestamp > DATE(NOW() - INTERVAL 1 WEEK)
                  AND c.comment_hash = -467734898
                  JOIN revision r2
                  ON r2.rev_id = r.rev_parent_id
                  AND r.rev_len < r2.rev_len )
GROUP BY p.page_title
HAVING (citbot_ts IS NULL) OR oabot_ts > citbot_ts;

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...