SQL
AخA
SELECT
SUBSTRING_INDEX(page_title, '/', 1) AS project,
SUM(
(
SELECT
COUNT(*)
FROM
revision
WHERE
page_id = rev_page
AND DATEDIFF(NOW(), rev_timestamp) <= 365
)
) AS count,
SUM(
(
SELECT
COUNT(*)
FROM
revision_userindex
WHERE
page_id = rev_page
AND DATEDIFF(NOW(), rev_timestamp) <= 365
AND rev_actor NOT IN (
SELECT
actor_id
FROM
user_groups
JOIN user ON user_id = ug_user
JOIN actor ON actor_user = user_id
WHERE
ug_group = 'bot'
)
)
) AS no_bots_count,
(
SELECT
page_is_redirect
FROM
page
WHERE
page_namespace = 4
AND page_title = project
) AS redirect
FROM
page
WHERE
(
page_title LIKE 'مشروع_ويكي\_%'
OR page_title LIKE 'WikiAfrica'
)
AND page_namespace BETWEEN 4
AND 5
AND page_is_redirect = 0
GROUP BY
project
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.