Incubator users (no of "wikis" edited) (Full, without portal pages)

Fork of Incubator users (no of "wikis" edited) by Nintendofan885

This query is marked as a draft This query has been published by Nintendofan885.

SQL

WITH 
	base_table AS (
      SELECT DISTINCT 
      REGEXP_SUBSTR(page_title, 'W[a-z]/[a-z]/+') AS prefix ,
      rev_id, 
        user_name,
        rev_timestamp
      FROM 
        revision rev
      JOIN 
        page p
        ON rev.rev_page = p.page_id
      JOIN 
        actor a
        ON rev.rev_actor = a.actor_id
      JOIN
      	user u
      	ON a.actor_user = u.user_id
      LEFT JOIN
      	user_groups ug
      	ON ug.ug_user = u.user_id
      WHERE 
        page_namespace IN (0, 1, 10, 11, 14, 15, 828, 829)


      HAVING 
        prefix <> ''),
   
   g1 AS (
     SELECT
     	user_name,
     	prefix,
     COUNT(rev_id) AS edit_count
     FROM base_table
    
     GROUP BY prefix, user_name
     -- HAVING COUNT(rev_id) >=5
   ),
   
   g2 AS (
     SELECT
     	user_name,
     COUNT(prefix) AS projects_edited
     FROM g1
     GROUP BY user_name)
     

SELECT *
FROM g2
ORDER BY projects_edited DESC;

SQL

Checking query status...