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 2 MONTH)
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.%'
               )
-- Exclude DOI removals identified by the decreased page size
JOIN revision r4
-- The rev_page selection is needed to use an index
ON  r4.rev_page = r2.rev_page
AND r4.rev_id = r2.rev_parent_id
AND r2.rev_len > r4.rev_len
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 2 MONTH)
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...