نقاش ويكيبيديا:تقارير قاعدة البيانات/المستخدمين حسب عدد إنشاء الصفحات (متضمنة البوتات)

Query

SELECT creations_ns0.user, first_edit.timestamp as first_edit, creations_ns0.count as article_creations, creations_ns10.count as template_creations, creations_ns12.count as help_creations, creations_ns14.count as category_creations, creations_ns100.count as portal_creations, creations_ns104.count as list_creations
FROM (SELECT COUNT(*) as count, creator.user as user
FROM (SELECT revision.rev_user_text as user
FROM (SELECT rev_page, MIN(rev_timestamp) as first_edit FROM page INNER JOIN revision ON page.page_id = revision.rev_page WHERE page.page_namespace = 0 AND page.page_is_redirect = 0 GROUP BY revision.rev_page)
as first_edit INNER JOIN revision ON first_edit.rev_page = revision.rev_page AND first_edit.first_edit = rev_timestamp) as creator GROUP BY creator.user) as creations_ns0
LEFT JOIN
(SELECT COUNT(*) as count, creator.user as user
FROM (SELECT revision.rev_user_text as user
FROM (SELECT rev_page, MIN(rev_timestamp) as first_edit FROM page INNER JOIN revision ON page.page_id = revision.rev_page WHERE page.page_namespace = 10 AND page.page_is_redirect = 0 GROUP BY revision.rev_page)
as first_edit INNER JOIN revision ON first_edit.rev_page = revision.rev_page AND first_edit.first_edit = rev_timestamp) as creator GROUP BY creator.user) as creations_ns10 ON creations_ns0.user = creations_ns10.user
LEFT JOIN
(SELECT COUNT(*) as count, creator.user as user
FROM (SELECT revision.rev_user_text as user
FROM (SELECT rev_page, MIN(rev_timestamp) as first_edit FROM page INNER JOIN revision ON page.page_id = revision.rev_page WHERE page.page_namespace = 12 AND page.page_is_redirect = 0 GROUP BY revision.rev_page)
as first_edit INNER JOIN revision ON first_edit.rev_page = revision.rev_page AND first_edit.first_edit = rev_timestamp) as creator GROUP BY creator.user) as creations_ns12 ON creations_ns0.user = creations_ns12.user
LEFT JOIN
(SELECT COUNT(*) as count, creator.user as user
FROM (SELECT revision.rev_user_text as user
FROM (SELECT rev_page, MIN(rev_timestamp) as first_edit FROM page INNER JOIN revision ON page.page_id = revision.rev_page WHERE page.page_namespace = 14 AND page.page_is_redirect = 0 GROUP BY revision.rev_page)
as first_edit INNER JOIN revision ON first_edit.rev_page = revision.rev_page AND first_edit.first_edit = rev_timestamp) as creator GROUP BY creator.user) as creations_ns14 ON creations_ns0.user = creations_ns14.user
LEFT JOIN
(SELECT COUNT(*) as count, creator.user as user
FROM (SELECT revision.rev_user_text as user
FROM (SELECT rev_page, MIN(rev_timestamp) as first_edit FROM page INNER JOIN revision ON page.page_id = revision.rev_page WHERE page.page_namespace = 100 AND page.page_is_redirect = 0 AND page.page_title NOT IN (SELECT page_title FROM page WHERE page_title LIKE "%/%") GROUP BY revision.rev_page)
as first_edit INNER JOIN revision ON first_edit.rev_page = revision.rev_page AND first_edit.first_edit = rev_timestamp) as creator GROUP BY creator.user) as creations_ns100 ON creations_ns0.user = creations_ns100.user
LEFT JOIN
(SELECT COUNT(*) as count, creator.user as user
FROM (SELECT revision.rev_user_text as user
FROM (SELECT rev_page, MIN(rev_timestamp) as first_edit FROM page INNER JOIN revision ON page.page_id = revision.rev_page WHERE page.page_namespace = 104 AND page.page_is_redirect = 0 GROUP BY revision.rev_page)
as first_edit INNER JOIN revision ON first_edit.rev_page = revision.rev_page AND first_edit.first_edit = rev_timestamp) as creator GROUP BY creator.user) as creations_ns104 ON creations_ns0.user = creations_ns104.user
INNER JOIN (SELECT revision.rev_user_text as user, min(rev_timestamp) as timestamp FROM revision GROUP BY rev_user_text) as first_edit ON creations_ns0.user = first_edit.user
GROUP BY creations_ns0.user ORDER BY article_creations DESC LIMIT 500;

نقاش

السلام عليكم @جار الله: هل يمكن إضافة نطاق نقاش المقالات ؟ --إبراهيم قاسم^راسلني 12:17، 30 أكتوبر 2019 (ت ع م)

وعليكم السلام @Mr. Ibrahem: سأعمل على ذلك في نهاية الأسبوع، تحياتي لك.--جار الله (نقاش) 12:19، 30 أكتوبر 2019 (ت ع م)

@Mr. Ibrahem:  تم.--جار الله (نقاش) 02:32، 5 نوفمبر 2019 (ت ع م)

رائع @جار الله: شكرا جزيلا --إبراهيم قاسم^راسلني 09:42، 5 نوفمبر 2019 (ت ع م)