users_by_the_number_of_pages_created

SQL

SELECT distinct
    u.user_name AS user_name,
    MIN(rev.rev_timestamp) AS first_edit_date,
    SUM(CASE WHEN p.page_namespace = 0 AND p.page_is_redirect = 0 THEN 1 ELSE 0 END) AS pages_created,
    SUM(CASE WHEN p.page_namespace = 10 AND p.page_is_redirect = 0 THEN 1 ELSE 0 END) AS template_created,
    SUM(CASE WHEN p.page_namespace = 12 AND p.page_is_redirect = 0 THEN 1 ELSE 0 END) AS help_created,
    SUM(CASE WHEN p.page_namespace = 14 AND p.page_is_redirect = 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 '%/%' THEN 1 ELSE 0 END) AS portals_created,
    SUM(CASE WHEN p.page_namespace = 0 AND p.page_is_redirect = 1 THEN 1 ELSE 0 END) AS redirect_created,
    SUM(CASE WHEN p.page_namespace = 828 AND p.page_is_redirect = 0 
        AND p.page_title NOT REGEXP '^.*?\/(ملعب|مختبر|شرح)/*.*?$'
        AND p.page_title NOT like '%/styles.css'
        AND p.page_title NOT like 'ملعب/%'
        THEN 1 ELSE 0 END) AS module_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 p ON rev.rev_page = p.page_id
WHERE
    ucase(a.actor_name) NOT LIKE ucase('%BOT')# COLLATE utf8mb4_general_ci
    AND a.actor_name NOT LIKE '%بوت%'# COLLATE utf8mb4_general_ci
    AND a.actor_name NOT IN (SELECT u2.user_name FROM user_groups ug JOIN user u2 ON ug.ug_user = u2.user_id WHERE ug.ug_group = 'bot')
    AND a.actor_id NOT IN ('2579643')
    AND rev.rev_parent_id = 0
    AND a.actor_user NOT IN (137877)
GROUP BY u.user_name
ORDER BY pages_created DESC
LIMIT 5
;
SQL

Checking query status...
