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 page
#LEFT JOIN actor_revision ON rev_actor = actor_id
JOIN revision AS cur ON cur.rev_page = page_id AND cur.rev_id=page_latest
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)*/
#AND ((CAST(cur.rev_len AS SIGNED)-CAST(prev.rev_len AS SIGNED))/CAST(prev.rev_len AS SIGNED))>=0.3
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.