use fawiki_p;

select rev_user, rev_user_text,

max(case when hour = '00' then edits else 0 end) as e00,

max(case when hour = '01' then edits else 0 end) as e01,

max(case when hour = '02' then edits else 0 end) as e02,

max(case when hour = '03' then edits else 0 end) as e03,

max(case when hour = '04' then edits else 0 end) as e04,

max(case when hour = '05' then edits else 0 end) as e05,

max(case when hour = '06' then edits else 0 end) as e06,

max(case when hour = '07' then edits else 0 end) as e07,

max(case when hour = '08' then edits else 0 end) as e08,

max(case when hour = '09' then edits else 0 end) as e09,

max(case when hour = '10' then edits else 0 end) as e10,

max(case when hour = '11' then edits else 0 end) as e11,

max(case when hour = '12' then edits else 0 end) as e12,

max(case when hour = '13' then edits else 0 end) as e13,

max(case when hour = '14' then edits else 0 end) as e14,

max(case when hour = '15' then edits else 0 end) as e15,

max(case when hour = '16' then edits else 0 end) as e16,

max(case when hour = '17' then edits else 0 end) as e17,

max(case when hour = '18' then edits else 0 end) as e18,

max(case when hour = '19' then edits else 0 end) as e19,

max(case when hour = '20' then edits else 0 end) as e20,

max(case when hour = '21' then edits else 0 end) as e21,

max(case when hour = '22' then edits else 0 end) as e22,

max(case when hour = '23' then edits else 0 end) as e23

from

(

  select rev_user, rev_user_text, mid(rev_timestamp, 9, 2) as hour, count(*) as edits

  from revision

  join

  (

    select rev_user as user_id, count(rev_id)

    from revision

    left join user_groups

    on rev_user = ug_user

    and ug_group = 'bot'

    where ug_group is null

    and rev_user <> 0

    group by rev_user

    having count(rev_id) > 3000

  ) freq

  on rev_user = user_id

  where rev_timestamp > '20150101000000'

  group by rev_user, rev_user_text, mid(rev_timestamp, 9, 2)

) hourly

group by rev_user, rev_user_text