Users with 5 edits, no. of NS0 pages and top 100 contributors

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 bots
SELECT 
    actor.actor_name AS user_name, 
    COUNT(*) 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
WHERE page_namespace = 0 
  AND page_is_redirect = 0
  AND revision.rev_id = page.page_first_rev -- Only consider the first revision of the page
  AND 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...