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.

Checking query status...