SQL
x
use nowiki_p;
select A.rev_user as ID, A.num_edits as Edits, coalesce(B.num_thanks, 0) as Thanks
from (select user_id, user_name
from user) as C
join (select rev_user, rev_user_text, count(rev_user) as num_edits
from revision
where
rev_timestamp < timestamp('2018-06-01')
and rev_timestamp >= timestamp('2017-06-01')
and rev_user != 0
group by rev_user
order by count(rev_user)) as A
on A.rev_user = C.user_id
#even though there's a much easier way to do this than use log_user_text, (using log_user instead)
#I'm going to use this code because I want to be consistent with how I did log_title
#Only users can thank and be thanked, so there's no worry that this might mess something up
left join (select log_title, count(log_title) as num_thanks
from logging_userindex
where
log_action = 'thank' and log_type='thanks'
and log_timestamp < timestamp('2018-06-01')
and log_timestamp >= timestamp('2017-06-01')
group by log_title) as B
on B.log_title = C.user_name
or B.log_title = A.rev_user_text
order by Edits;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.