get the total edit count of a Mediawiki user

!readme-analytics

these are some sql queries to do analytics on the mediawiki database.

001_counter1.sql

--To get detailed edit metadata of the user include
--when, page, namesapace and the revision comment.
------------------------------------------------------
  SELECT rev_timestamp, page_title, page_namespace, rev_comment
    FROM revision_userindex 
INNER JOIN page 
        ON page_id = rev_page
   WHERE rev_user_text LIKE "Jimbo Wales";
ORDER BY rev_timestamp ASC;

002_counter2.sql

--get user edit count 
----------------------------
SELECT COUNT(*) AS count 
  FROM revision_userindex 
 WHERE rev_user_text = "Jimbo Wales";

003_counter3.sql

--this example lets you have a summary of a users edit restricted to a time period
--but you need to supply 
--$user_name - the user name 
--$start_timestamp, $end_timestamp - in the form: 201301010101010 
SELECT page_namespace, COUNT(*), rev_timestamp
  FROM  revision_userindex 
   JOIN page 
     ON page_id = rev_page 
 WHERE rev_user_text = "$user_name"
  AND rev_timestamp  BETWEEN "$start_timestamp" AND "$end_timestamp"
 GROUP BY page_namespace;

004_counter4.sql

--To get user namespace edit count by month
------------------------------------------------------
SELECT EXTRACT(YEAR_MONTH FROM rev_timestamp) AS YM 
       page_namespace as NS, 
       COUNT(*)
  FROM revision_userindex JOIN page ON page_id = rev_page 
 WHERE rev_user_text = "Jimbo Wales"
 GROUP BY NS,YM
ORDER BY YM , NS ASC;

010_daily_counter.sql

--gets daily edit count for use with a heat map calander
--$user_name="Jimbo Wales"
--$start_time=20010327010101
--$end_time=20020101010101
SELECT EXTRACT(YEAR FROM rev_timestamp)  AS YY,
       EXTRACT(MONTH FROM rev_timestamp) AS MM,
       EXTRACT(DAY FROM rev_timestamp)   AS DD,
       COUNT(*)
  FROM revision_userindex 
       JOIN page 
       ON page_id = rev_page 
 WHERE rev_user_text = '{$user_name}'
        AND rev_timestamp  
           BETWEEN {$start_time}
               AND {$end_time}
 GROUP BY YY,MM,DD
 ORDER BY YY,MM,DD ASC;

020_group_daily_counter.sql

--gets daily edit count for use with a heat map calander
--$user_name='Jimbo Wales', 'OrenBochman' 
--$start_time=20010327010101
--$end_time=20020101010101
SELECT EXTRACT(YEAR FROM rev_timestamp)  AS YY,
       EXTRACT(MONTH FROM rev_timestamp) AS MM,
       EXTRACT(DAY FROM rev_timestamp)   AS DD,
       COUNT(*)
  FROM revision_userindex 
       JOIN page 
       ON page_id = rev_page 
 WHERE rev_user_text IN ({$user_names})
        AND rev_timestamp  
           BETWEEN {$start_time}
               AND {$end_time}
 GROUP BY YY,MM,DD
 ORDER BY YY,MM,DD ASC

friends by top articles.sql

Select rev_user, rev_user_text, COUNT(rev_page)
  From revision_userindex
  INNER JOIN page ON page_id = rev_page 
 Where rev_page in 
   (SELECT rev_page   
      FROM revision_userindex   
     INNER JOIN page ON page.page_id = rev_page  
     WHERE rev_user_text = "Jimbo Wales" 
       AND page_namespace IN (0,1)    
     GROUP BY rev_page 
    HAVING count(rev_page) > 20
     ORDER BY COUNT(*) DESC
    ) 
 AND rev_user != 1
 GROUP BY rev_user
 HAVING count(rev_page) > 10  
ORDER BY COUNT(*) DESC
  LIMIT 6;

top articles & their talk sql

 SELECT page_title, count(page_title)    
   FROM revision_userindex   
  INNER JOIN page ON page_id = rev_page  
  INNER JOIN namespaces ON page_namespace=i   
  WHERE rev_user_text LIKE "Jimbo Wales" 
    AND page_namespace IN (0,1,2)    
  GROUP BY page_title 
 HAVING count(page_title) > 10  
  ORDER BY COUNT(*) DESC
  LIMIT 30;