psm -- test characteristics

SQL

use plwiki_p;

select A.user_name as Username, A.user_registration as Registration_Date, B.num_edits as Num_Edits, 
		coalesce(C.num_thanks, 0) as Num_Thanks, coalesce(D.will_be_thanked, 0) as Thanked_Tomorrow,
        coalesce(E.thanks, 0) as Weeks_Thanks, coalesce(F.edits, 0) as Weeks_Edits, coalesce(G.edits, 0) as Future_Edits

from (select user_name, user_registration
      from user
      where (
        user_registration < timestamp('2017-02-08'))) as A

join (select rev_user_text, count(rev_user_text) as num_edits
	from revision
    where (
      rev_timestamp < timestamp('2017-05-08')
      and rev_timestamp >= timestamp('2017-02-08')
      and rev_user != 0)
      group by rev_user_text) as B
      
on A.user_name = B.rev_user_text
    
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('2017-05-08') 
        and log_timestamp >= timestamp('2017-02-08'))
    group by log_title) as C

on A.user_name = C.log_title

left join (select log_title, count(log_title) as will_be_thanked
          from logging_userindex
      	  where (
      		  log_action = 'thank' and log_type='thanks'
		      and log_timestamp < timestamp('2017-05-09')
              and log_timestamp >= timestamp('2017-05-08'))
      	  group by log_title) as D

on A.user_name = D.log_title

left join (select log_title, count(log_title) as thanks
          from logging_userindex
      	  where (
      		  log_action = 'thank' and log_type='thanks'
		      and log_timestamp < timestamp('2017-05-08')
              and log_timestamp >= timestamp('2017-05-01'))
      	  group by log_title) as E

on A.user_name = E.log_title

left join (select rev_user_text, count(rev_user_text) as edits
	from revision
    where (
      rev_timestamp < timestamp('2017-05-08')
      and rev_timestamp >= timestamp('2017-05-01')
      and rev_user != 0)
      group by rev_user_text) as F

on A.user_name = F.rev_user_text

left join (select rev_user_text, count(rev_user_text) as edits
	from revision
    where (
      rev_timestamp < timestamp('2017-06-09')
      and rev_timestamp >= timestamp('2017-05-09')
      and rev_user != 0)
      group by rev_user_text) as G
      
on A.user_name = G.rev_user_text    
order by B.num_edits;
SQL

Checking query status...
