Fork of
trwiki:Articles which might contain unreliable sources by external link
by Evolutionoftheuniverse
This query is marked as a draft
This query has been published
by Evolutionoftheuniverse.
SQL
AخA
USE trwiki_p;
SELECT page_title, prev.rev_len, page_len, IFNULL(((CAST(page_len AS SIGNED)-CAST(prev.rev_len AS SIGNED))/CAST(prev.rev_len AS SIGNED)),1) as delta
FROM revision AS cur
#LEFT JOIN actor_revision ON rev_actor = actor_id
JOIN page ON cur.rev_page = page_id
LEFT JOIN revision AS prev ON prev.rev_page = cur.rev_page
AND prev.rev_deleted=0
#AND prev.rev_timestamp<(SELECT TIMESTAMP("2020-01-01 00:00:00"))
AND prev.rev_timestamp = (SELECT MAX(rev_timestamp) FROM revision WHERE prev.rev_page = page_id
AND rev_deleted=0
AND rev_parent_id = 0
AND page_namespace=0
AND rev_timestamp<(SELECT TIMESTAMP("2020-01-01 00:00:00"))
group by page_title)
WHERE
cur.rev_parent_id = 0
AND page_namespace = 0
AND page_is_redirect=0
AND cur.rev_deleted=0
#AND cur.rev_timestamp <= (SELECT TIMESTAMP("2020-12-31 23:59:59"))
#AND cur.rev_timestamp >= (SELECT TIMESTAMP("2020-01-01 00:00:00"))
AND cur.rev_timestamp = (SELECT MAX(rev_timestamp) FROM revision WHERE cur.rev_page = page_id
AND rev_deleted=0
AND rev_parent_id = 0
AND page_namespace=0
AND rev_timestamp <= (SELECT TIMESTAMP("2020-12-31 23:59:59"))
AND rev_timestamp >= (SELECT TIMESTAMP("2020-01-01 00:00:00"))
group by page_title)
AND page_len>=15000
GROUP BY page_title HAVING delta>=0.3
ORDER BY page_len DESC, delta DESC, page_title ASC;
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.