This query is marked as a draft This query has been published by لوقا.

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.

Checking query status...