Research:Top edited articles in 2014
I got a request for the most edited articles in 2014.
Methods
editCreating 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;
Results
editHere's the top for the whole year.
Here's the top by month since it tends to fluctuate (see also full list).