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

SQL

-- Set the database to Tamil Wikipedia
USE tawiki_p;

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

-- Task 2: Total number of pages in namespace 0
SELECT COUNT(*) AS total_pages_in_ns0
FROM page
WHERE page_namespace = 0;

-- Task 3: Top 100 users with the number of pages they have created in namespace 0
SELECT user.user_name, COUNT(*) AS pages_created
FROM page
JOIN revision ON page.page_id = revision.rev_page
JOIN user ON revision.rev_user = user.user_id
WHERE page_namespace = 0 AND page_is_redirect = 0
GROUP BY user.user_name
ORDER BY pages_created DESC
LIMIT 100;
SQL

Checking query status...
