You are seeing those gaps because the method you use to calculate account age is not correct. Run this query:
select user_id, user_registration, (now() - str_to_date(user_registration, '%Y%m%d%H%i%s'))/86400 as registered_time from user where user_id >= 25650026 and user_id <= 25650027; And you will see that those two accounts which are created back to back, one at the end of 6/30/2015 and one at the beginning of 7/1/2015 will return an account age of ~2000 and ~1181 respectively (two numbers that are not that close). Instead, run this: select user_id, user_registration, datediff(now(), str_to_date(user_registration, '%Y%m%d%H%i%s')) as acct_age from user where user_id >= 25650026 and user_id <= 25650027; And you will get the account age in days (33 and 34 days in this example). On Sun, Aug 2, 2015 at 1:12 PM, Golden Ring <[email protected]> wrote: > I'm hoping that someone here can explain some query weirdness to me. > > I'm trying to get a list of the n most-recently-registered users who have > done page moves, and how many page moves they've done. To do this, I'm > using this query: > > select > user_id, > user_name, > (now() - str_to_date(user_registration, '%Y%m%d%H%i%s'))/86400 as > registered_time, > count(log_id) as moves > from > user, > logging_userindex > where > user_id = log_user > and (log_action = 'move' or log_action = 'move_redir') > group by user_id > order by user_id desc > limit {}; > > For a limit of about 100 rows, this gives plausible-looking results. But > increasing the limit to 1000 gives some pretty bizarre results. There are > big gaps in the registered_time variable. The range of the registered_time > variable is about 0 - 4,000 days, but there are no results with registered > times between approx 350 - 1160 days, 1500 - 2300 days, or 2670 to 3480 > days. > > July 2013 is about 700 days ago, but a query to find users with page moves > who registered on July 15, 2013 shows that such users do exist: > > select count(*) from user, logging_userindex where user_id = log_user and > log_action='move' and user_registration like '20130715%'; > > Result: 63 move actions from users registered on 20130715. > > What's going on here? I assume I've simply got the first query wrong in > some way, but I can't see how. > > Note that all of the above is against the enwiki replica. > > Thanks for any help, > GoldenRing > > > _______________________________________________ > Labs-l mailing list > [email protected] > https://lists.wikimedia.org/mailman/listinfo/labs-l > >
_______________________________________________ Labs-l mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/labs-l
