Analysis of social communication and information by user

calc difs of new usersdiff calculation

SELECT CAST(parentrevision.rev_len AS SIGNED) - CAST(revision.rev_len AS SIGNED) AS diff FROM revision JOIN revision AS parentrevision ON parentrevision.rev_id = revision.rev_parent_id WHERE revision.rev_parent_id <> 0 ORDER BY revision.rev_id DESC LIMIT 1;

find latest new users new.sql

set @start:=20130722000000;
set @end:=20130724000000;
SELECT rev_user, count(*) , min(rev_timestamp)
FROM revision_userindex 
WHERE rev_timestamp > @start 
GROUP BY rev_user 
HAVING count(rev_id) > 3
Limit 10;

SELECT rev_user FROM revision_userindex WHERE rev_timestamp BETWEEN @start AND @end ORDER BY rev_timestamp ASC Limit 10;
SELECT rev_user FROM revision_userindex WHERE rev_timestamp > 20130723000000  Limit 10;
SELECT rev_user FROM revision_userindex WHERE rev_timestamp > 20130723000000 GROUP BY rev_user  Limit 10;
SELECT rev_user FROM revision_userindex WHERE rev_timestamp BETWEEN @start AND @end  GROUP BY rev_user  Limit 10;
-- this takes an hour to run possibly due to the group by...
SELECT user_name, user_editcount FROM user JOIN revision_userindex ON user_id = rev_user
WHERE user_editcount Between 3 AND 100 GROUP BY by user_id  Having Min(rev_timestamp) > 20130723000000
ORDER BY user_editcount DESC LIMIT 10;