Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
This query is marked as a draft
This query has been published
by
لوقا
.
Toggle Highlighting
SQL
/* SELECT YEAR(creation_date) AS creation_year, actor_name, COUNT(*) AS category_count FROM ( SELECT page_id, MIN(rev_timestamp) AS creation_date, actor_name FROM page JOIN revision ON page_id = rev_page INNER JOIN actor ON rev_actor = actor_id WHERE page_namespace = 14 -- Category namespace AND ( UPPER(actor_name) LIKE UPPER("%BOT") COLLATE utf8_general_ci OR actor_name LIKE "%بوت%" COLLATE utf8_general_ci OR actor_name IN ( SELECT user_name FROM user_groups INNER JOIN user ON user_id = ug_user WHERE ug_group = "bot" ) ) GROUP BY page_id, actor_name ) AS first_revisions GROUP BY creation_year, actor_name ORDER BY creation_year, actor_name; */ SELECT actor_name AS name, SUM(CASE WHEN YEAR(creation_date) = 2015 THEN 1 ELSE 0 END) AS `2015`, SUM(CASE WHEN YEAR(creation_date) = 2016 THEN 1 ELSE 0 END) AS `2016`, SUM(CASE WHEN YEAR(creation_date) = 2017 THEN 1 ELSE 0 END) AS `2017`, SUM(CASE WHEN YEAR(creation_date) = 2018 THEN 1 ELSE 0 END) AS `2018`, SUM(CASE WHEN YEAR(creation_date) = 2019 THEN 1 ELSE 0 END) AS `2019`, SUM(CASE WHEN YEAR(creation_date) = 2020 THEN 1 ELSE 0 END) AS `2020`, SUM(CASE WHEN YEAR(creation_date) = 2021 THEN 1 ELSE 0 END) AS `2021`, SUM(CASE WHEN YEAR(creation_date) = 2022 THEN 1 ELSE 0 END) AS `2022`, SUM(CASE WHEN YEAR(creation_date) = 2023 THEN 1 ELSE 0 END) AS `2023` FROM ( SELECT page_id, MIN(rev_timestamp) AS creation_date, actor_name FROM page JOIN revision ON page_id = rev_page INNER JOIN actor ON rev_actor = actor_id WHERE page_namespace = 14 -- Category namespace AND ( UPPER(actor_name) LIKE UPPER("%BOT") COLLATE utf8_general_ci OR actor_name LIKE "%بوت%" COLLATE utf8_general_ci OR actor_name IN ( SELECT user_name FROM user_groups INNER JOIN user ON user_id = ug_user WHERE ug_group = "bot" ) ) GROUP BY page_id, actor_name ) AS first_revisions WHERE creation_date = ( SELECT MIN(rev_timestamp) FROM revision WHERE rev_page = first_revisions.page_id ) GROUP BY actor_name 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
.
Submit Query
Stop Query
All SQL code is licensed under
CC0 License
.
Checking query status...