SQL
AخA
USE wikidatawiki_p;
SELECT rev1.rev_timestamp, rev1.rev_user_text, page_title, rev1.rev_id AS revid, rev1.rev_comment, COUNT(links.pl_from) AS backlinks_count
FROM (SELECT * FROM page WHERE page_namespace = 0 AND page_is_redirect = 0) AS items
JOIN (SELECT * FROM page_props WHERE pp_propname = 'wb-sitelinks' AND pp_value = 0) AS props
ON pp_page = page_id
JOIN (SELECT * FROM revision WHERE rev_comment LIKE '/* wbsetsitelink-remove:%') AS rev1
ON rev1.rev_page = page_id
LEFT JOIN (SELECT * FROM pagelinks WHERE pl_namespace = 0 AND pl_from_namespace = 0) AS links
ON links.pl_title = page_title
LEFT JOIN (SELECT * FROM user_groups WHERE ug_group = 'bot') AS bots
ON ug_user = rev_user
LEFT JOIN (SELECT * FROM revision WHERE rev_comment LIKE '/* wbsetsitelink-remove:%') AS rev2
ON rev2.rev_page = rev1.rev_page AND rev2.rev_id > rev1.rev_id
WHERE rev2.rev_timestamp IS NULL
AND ug_group IS NULL
GROUP BY page_title # HAVING COUNT(links.pl_from) = 0
ORDER BY COUNT(links.pl_from), rev1.rev_timestamp;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.