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.