Thanks by year

SQL

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;
SQL

Checking query status...
