Untitled query #88354

SQL

-- Set the database to Tamil Wikipedia
USE tawiki_p;

-- Task 3: Top 100 users with their user name and the number of unique pages they created in namespace 0,
-- excluding redirects and disambiguation pages
SELECT 
    actor.actor_name AS user_name, 
    COUNT(DISTINCT page.page_id) AS unique_pages_created
FROM page
JOIN revision ON page.page_id = revision.rev_page
JOIN actor ON revision.rev_actor = actor.actor_id
LEFT JOIN user_groups ON actor.actor_user = user_groups.ug_user
LEFT JOIN categorylinks ON page.page_id = categorylinks.cl_from
WHERE page_namespace = 0 
  AND page_is_redirect = 0 -- Exclude redirect pages
  AND revision.rev_id = ( -- Only count the first revision (page creator)
      SELECT MIN(rev_id) 
      FROM revision 
      WHERE revision.rev_page = page.page_id
  )
  AND (categorylinks.cl_to IS NULL OR categorylinks.cl_to != 'Disambiguation_pages') -- Exclude disambiguation pages
  AND (user_groups.ug_group IS NULL OR user_groups.ug_group != 'bot') -- Exclude bot accounts
GROUP BY actor.actor_name
ORDER BY unique_pages_created DESC
LIMIT 100;
SQL

Checking query status...
