Fork of
Article creators by volume past year
by FShbib
This query is marked as a draft
This query has been published
by BilledMammal.
SQL
AخA
SELECT actor_name,
COUNT(*) AS "Year total",
SUM(CASE WHEN SUBSTRING(rev_timestamp,1,6) = 202109 THEN 1 ELSE 0 END) AS "Sep 2021",
SUM(CASE WHEN SUBSTRING(rev_timestamp,1,6) = 202110 THEN 1 ELSE 0 END) AS "Oct 2021",
SUM(CASE WHEN SUBSTRING(rev_timestamp,1,6) = 202111 THEN 1 ELSE 0 END) AS "Nov 2021",
SUM(CASE WHEN SUBSTRING(rev_timestamp,1,6) = 202112 THEN 1 ELSE 0 END) AS "Dec 2021",
SUM(CASE WHEN SUBSTRING(rev_timestamp,1,6) = 202201 THEN 1 ELSE 0 END) AS "Jan 2022",
SUM(CASE WHEN SUBSTRING(rev_timestamp,1,6) = 202202 THEN 1 ELSE 0 END) AS "Feb 2022",
SUM(CASE WHEN SUBSTRING(rev_timestamp,1,6) = 202203 THEN 1 ELSE 0 END) AS "Mar 2022",
SUM(CASE WHEN SUBSTRING(rev_timestamp,1,6) = 202204 THEN 1 ELSE 0 END) AS "Apr 2022",
SUM(CASE WHEN SUBSTRING(rev_timestamp,1,6) = 202205 THEN 1 ELSE 0 END) AS "May 2022",
SUM(CASE WHEN SUBSTRING(rev_timestamp,1,6) = 202206 THEN 1 ELSE 0 END) AS "Jun 2022",
SUM(CASE WHEN SUBSTRING(rev_timestamp,1,6) = 202207 THEN 1 ELSE 0 END) AS "Jul 2022",
SUM(CASE WHEN SUBSTRING(rev_timestamp,1,6) = 202208 THEN 1 ELSE 0 END) AS "Aug 2022"
FROM revision
JOIN actor_revision ON actor_id = rev_actor
JOIN page ON page_id = rev_page
LEFT JOIN categorylinks ON cl_from = page_id AND (
cl_to = 'Disambiguation_pages' OR
cl_to LIKE '%disambiguation_pages' OR
cl_to LIKE 'List%' OR
cl_to = 'Surnames' OR
cl_to = 'Given names')
WHERE rev_parent_id = 0
AND page_namespace = 0
AND page_is_redirect = 0
AND rev_timestamp >= '20210901'
AND rev_timestamp <= '20220901'
AND cl_to IS NULL
GROUP BY actor_name
HAVING COUNT(*) >= 100
ORDER BY COUNT(*) DESC;
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.