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 son1aytop
,sum(log_timestamp > date_add(now(), INTERVAL -6 MONTH)) as son6aytop,sum(log_timestamp > now() - 10000000000) as son12aytop
,sum(log_timestamp > date_add(now(), INTERVAL -18 MONTH)) as son18aytop,sum(log_timestamp > now() - 20000000000) as son24aytop
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 son24aytop, son12aytop, son6aytop
;

-- 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(sum(log_timestamp > date_add(now(), INTERVAL -30 DAY))/30,2) as son1ayort
,round(sum(log_timestamp > date_add(now(), INTERVAL -183 DAY))/183,2) as son6ayort
,round(sum(log_timestamp > date_add(now(), INTERVAL -365 DAY))/365,2) as son12ayort
,round(sum(log_timestamp > date_add(now(), INTERVAL -548 DAY))/548,2) as son18ayort
,round(sum(log_timestamp > date_add(now(), INTERVAL -731 DAY))/731,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 son24ayort, son12ayort, son6ayort
;
SQL

Checking query status...
