use eswiki_p;

select ipb_by_text, ipb_reason, user_registration, user_editcount, ipb_timestamp, user_name

   , timestampdiff(MINUTE, user_registration, ipb_timestamp) AS minutos, ipb_expiry

   , (

     select count(rev_id) 

        from page 

        left join revision 

        on rev_page = page_id

        where page_title = user_name 

        and page_namespace = 3

        and rev_id is not null

        and rev_timestamp <= ipb_timestamp) discusion_antes

    , (

     select count(rev_id) 

        from page 

        left join revision 

        on rev_page = page_id

        where page_title = user_name 

        and page_namespace = 3

        and rev_id is not null

        and rev_timestamp > ipb_timestamp) discusion_despues

     , (

     select count(rev_id) 

        from page 

        left join revision 

        on rev_page = page_id

        left join user_groups

        on ug_user = rev_user

        where page_title = user_name 

        and page_namespace = 3

        and ug_group = 'bot'

        and rev_id is not null

        and rev_timestamp <= ipb_timestamp) discusion_antes_bot

from user 

left join ipblocks

  on user_id = ipb_user

where ipb_timestamp between '20170101000000' and '20170701000000'

  and ipb_user is not null

/*limit 100*/