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.thanks, 0) as Days_Thanks, coalesce(F.edits, 0) as Days_Edits, coalesce(G.edits, 0) as Future_Edits,
A.user_editcount as All_Edits
from (select user_name, user_registration, user_editcount
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, count(log_title) as thanks
from logging_userindex
where (
log_action = 'thank' and log_type='thanks'
and log_timestamp < timestamp('2017-08-08')
and log_timestamp >= timestamp('2017-08-07'))
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-08-08')
and rev_timestamp >= timestamp('2017-08-07')
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-08-10')
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.