hasek - Quarry

SQL

USE trwiki_p;
select user_name as kullanici
-- , lg.log_timestamp as son_log, lg.log_type as son_log_tip, lg.log_action as son_log_aksiyon
,sum(log_timestamp > date_add(now(), INTERVAL -1 MONTH)) as son1ay
,sum(log_timestamp > date_add(now(), INTERVAL -6 MONTH))/6 as son6ay,sum(log_timestamp > now() - 10000000000)/12 as son12ay
,sum(log_timestamp > date_add(now(), INTERVAL -18 MONTH))/18 as son18ay,sum(log_timestamp > now() - 20000000000)/24 as son24ay
from user_groups left join 
      (select * from logging_userindex where log_timestamp > NOW()-2*10000000000 
       and log_type in ("rights" ,"block","delete","protect","abusefilter"
                            ) order by log_timestamp desc) as lg on lg.log_user = ug_user
                            join user on user_id = ug_user
where ug_group = 'sysop'
group by user_id
order by son24ay, son12ay, son6ay
;

-- gunluk ortalamalar --
select user_name as kullanici
-- , lg.log_timestamp as son_log, lg.log_type as son_log_tip, lg.log_action as son_log_aksiyon
,round(mean(log_timestamp > date_add(now(), INTERVAL -1 MONTH)),2) as son1ayort
,round(mean(log_timestamp > date_add(now(), INTERVAL -6 MONTH)),2) as son6ayort
,round(mean(log_timestamp > now() - 10000000000),2) as son12ayort
,round(mean(log_timestamp > date_add(now(), INTERVAL -18 MONTH)),2) as son18ayort
,round(mean(log_timestamp > now() - 20000000000),2) as son24ayort
from user_groups left join 
      (select * from logging_userindex where log_timestamp > NOW()-2*10000000000 
       and log_type in ("rights" ,"block","delete","protect","abusefilter"
                            ) order by log_timestamp desc) as lg on lg.log_user = ug_user
                            join user on user_id = ug_user
where ug_group = 'sysop'
group by user_id
order by son24ay, son12ay, son6ay
;
SQL

Checking query status...
