monthly statistics of jawiki

SQL

use jawiki_p;
select
  user2.username as 利用者名,
  user2.registered as 登録日,
  user2.first_edit as 初投稿日,
  user2.last_edit as 最終投稿日,
  (case
  	when log.log_action = "block" then "ブロック"
    when log.log_action = "reblock" then "再ブロック"
    when log.log_action = "unblock" then "ブロック解除"
    else null
   end) as ブロック/解除の別,
  date( log.log_timestamp ) as 実行日,
  log.log_params as 期間など
from (
  select
    user.user_name as username,
    date( user.user_registration ) as registered,
    min( date( rev.rev_timestamp ) ) as first_edit,
    max( date( rev.rev_timestamp ) ) as last_edit
  from user
  inner join revision_userindex as rev on user.user_id = rev.rev_user
  where user.user_name in ( "Tonomuraayuta", "Tanka~jawiki", "魔法陣", "和暦除去行為取消し", "和暦除去行為取消",
						  "武王", "Disputed", "志賀 慶一", "弱", "Inception2010" )
  group by user.user_id, rev.rev_user, user.user_name, user.user_registration
) as user2
left outer join (
  select log2.log_type as type, log2.log_action, log2.log_title as blocked_user, log2.log_timestamp, log2.log_params
  from logging_userindex as log2
  where log_title in ( "Tonomuraayuta", "Tanka~jawiki", "魔法陣", "和暦除去行為取消し", "和暦除去行為取消",
					   "武王", "Disputed", "志賀 慶一", "弱", "Inception2010" ) and
	    log_type = "block"
) as log on user2.username = log.blocked_user
order by user2.registered, user2.first_edit, user2.last_edit, log.log_timestamp
SQL

Checking query status...
