users_by_the_number_of_pages_created

SQL

SELECT
    u.user_name AS user_name,
    MIN(r.first_edit_date) AS first_edit_date,
    SUM(CASE WHEN p.page_namespace = 0 AND p.page_is_redirect = 0 AND r.rev_parent_id = 0 THEN 1 ELSE 0 END) AS pages_created,
    SUM(CASE WHEN p.page_namespace = 10 AND p.page_is_redirect = 0 AND r.rev_parent_id = 0 THEN 1 ELSE 0 END) AS template_created,
    SUM(CASE WHEN p.page_namespace = 12 AND p.page_is_redirect = 0 AND r.rev_parent_id = 0 THEN 1 ELSE 0 END) AS help_created,
    SUM(CASE WHEN p.page_namespace = 14 AND p.page_is_redirect = 0 AND r.rev_parent_id = 0 THEN 1 ELSE 0 END) AS category_created,
    SUM(CASE WHEN p.page_namespace = 100 AND p.page_is_redirect = 0 AND p.page_title NOT LIKE '%/%' AND r.rev_parent_id = 0 THEN 1 ELSE 0 END) AS portals_created,
    SUM(CASE WHEN p.page_namespace = 0 AND p.page_is_redirect = 1 AND r.rev_parent_id = 0 THEN 1 ELSE 0 END) AS redirect_created
FROM user u
JOIN actor a ON a.actor_user = u.user_id
LEFT JOIN (
    SELECT rev_actor, MIN(rev_timestamp) AS first_edit_date
    FROM revision
    GROUP BY rev_actor
) r ON a.actor_id = r.rev_actor
LEFT JOIN revision r2 ON a.actor_id = r2.rev_actor
LEFT JOIN page p ON r2.rev_page = p.page_id
LEFT JOIN user_groups ug ON u.user_id = ug.ug_user
WHERE
    UPPER(a.actor_name) NOT LIKE UPPER('%BOT')
    AND a.actor_name NOT LIKE '%بوت%'
    AND a.actor_id NOT IN (2579643)
    AND u.user_id NOT IN (137877)
    AND ug.ug_group = 'bot'
GROUP BY u.user_name
ORDER BY pages_created DESC
LIMIT 5;
SQL

Checking query status...
