users_by_the_number_of_pages_created

SQL

SELECT
    u.user_name AS user_name,
    MIN(rev.rev_timestamp) AS first_edit_date,
    SUM(CASE WHEN page.page_namespace = 0 AND page.page_is_redirect = 0 AND rev.rev_parent_id = 0 THEN 1 ELSE 0 END) AS pages_created,
    SUM(CASE WHEN page.page_namespace = 10 AND page.page_is_redirect = 0 AND rev.rev_parent_id = 0 THEN 1 ELSE 0 END) AS template_created,
    SUM(CASE WHEN page.page_namespace = 12 AND page.page_is_redirect = 0 AND rev.rev_parent_id = 0 THEN 1 ELSE 0 END) AS help_created,
    SUM(CASE WHEN page.page_namespace = 14 AND page.page_is_redirect = 0 AND rev.rev_parent_id = 0 THEN 1 ELSE 0 END) AS category_created,
    SUM(CASE WHEN page.page_namespace = 100 AND page.page_is_redirect = 0 AND rev.rev_parent_id = 0 AND page.page_title NOT LIKE '%/%' THEN 1 ELSE 0 END) AS portals_created,
    SUM(CASE WHEN page.page_namespace = 0 AND page.page_is_redirect = 1 AND rev.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
JOIN revision rev ON rev.rev_actor = a.actor_id
JOIN page ON rev.rev_page = page.page_id
WHERE
    ucase(a.actor_name) NOT LIKE ucase('%BOT')
    AND a.actor_name = 'Amr F.Nagy'
    AND a.actor_name NOT LIKE '%بوت%'
    AND a.actor_name NOT IN (SELECT user_name FROM user_groups ug JOIN user ON ug.ug_user = user.user_id WHERE ug.ug_group = 'bot')
    AND a.actor_id NOT IN ('2579643')
    AND a.actor_user NOT IN (137877)
GROUP BY u.user_name
ORDER BY pages_created DESC
LIMIT 5;
SQL

Checking query status...
