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

SQL

-- Set the database to Tamil Wikipedia
USE tawiki_p;

-- Set the database to Tamil Wikipedia
USE tawiki_p;

-- Task 1: Total number of registered users with more than 5 edits
SELECT COUNT(DISTINCT actor.actor_id) AS registered_users_with_more_than_5_edits
FROM actor
JOIN revision ON revision.rev_actor = actor.actor_id
JOIN user ON user.user_id = actor.actor_user
WHERE user.user_registration IS NOT NULL
GROUP BY actor.actor_id
HAVING COUNT(revision.rev_id) > 5;

-- Task 2: Count total non-redirect pages in namespace 0
SELECT COUNT(*) AS total_ns0_pages
FROM page
WHERE page_namespace = 0 
  AND page_is_redirect = 0;


-- 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(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
WHERE page_namespace = 0 
  AND page_is_redirect = 0
  AND revision.rev_id = ( -- Get the first revision for each page
      SELECT MIN(rev_id) 
      FROM revision 
      WHERE revision.rev_page = page.page_id
  )
  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 350;
SQL

Checking query status...
