This query is marked as a draft This query has been published by Swagoel.

SQL

x
 
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.deleted, 0) as Deleted, coalesce(F.user_group, 0) as User_Group, coalesce(G.edits, 0) as Future_Edits,
        A.user_editcount as All_Edits
from (select user_name, user_registration, user_editcount, user_id
      from user
      where (
        user_registration < timestamp('2017-05-08'))) as A
join (select rev_user_text, count(rev_user_text) as num_edits
    from revision
    where (
      rev_timestamp < timestamp('2017-08-08')
      and rev_timestamp >= timestamp('2017-05-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-08-08') 
        and log_timestamp >= timestamp('2017-05-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-08-09')
              and log_timestamp >= timestamp('2017-08-08'))
          group by log_title) as D
on A.user_name = D.log_title
left join (select log_title, sum(log_deleted) as deleted
          from logging_userindex
          where (
              log_timestamp < timestamp('2017-08-08')
              and log_timestamp >= timestamp('2017-05-08'))
          group by log_title) as E
on A.user_name = E.log_title
left join (select ug_user, count(distinct ug_group) as user_group
    from user_groups
      group by ug_user) as F
on A.user_id = F.ug_user
left join (select rev_user_text, count(rev_user_text) as edits
    from revision
    where (
      rev_timestamp < timestamp('2017-09-09')
      and rev_timestamp >= timestamp('2017-08-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;    
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.

Checking query status...