أكثر المستخدمين في إنشاء مقالات المساعدة والشروحات

SQL

SELECT  a.actor_name AS first_user, COUNT(new_posts.page_title) AS post_count_by_user
FROM (
    SELECT p.page_id, p.page_title, MIN(r.rev_timestamp) AS first_revision
    FROM revision r
    JOIN page p ON p.page_id = r.rev_page
    WHERE (page_namespace = 12 or (page_namespace in (10,828) and (page_title  like '%شرح')))

        AND p.page_is_redirect = 0 
        AND r.rev_parent_id = 0
    GROUP BY p.page_id
    HAVING first_revision BETWEEN 20230101000000 AND 20231231235959
) AS new_posts
JOIN revision r ON new_posts.page_id = r.rev_page AND new_posts.first_revision = r.rev_timestamp
JOIN actor a ON r.rev_actor = a.actor_id
GROUP BY first_user
ORDER BY post_count_by_user DESC
LIMIT 10;

SQL

Checking query status...