Research:Top edited articles in 2014
completed research project.
I got a request for the most edited articles in 2014.
Creating the monthly edits table:
SELECT LEFT(rev_timestamp, 6) AS month, rev_page AS page_id, COUNT(*) AS edits FROM revision WHERE rev_timestamp BETWEEN "2014" AND "2015" GROUP BY month, page_id ORDER BY month ASC, edits DESC;
Here's the aggregation up to a yearly total:
SELECT "2014" AS year, page_id, SUM(edits) AS edits FROM staging.monthly_page_edits GROUP BY page_id;
Here's the top for the whole year.
Here's the top by month since it tends to fluctuate (see also full list).