use fawiki_p;

select rev_user, rev_user_text,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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