|  9371156 | research | 10.64.36.103:54651 | log    | Query   | 5543 | Queried about 680000 rows   | 
select day,
        sum(if(actions like '%saveAttempt%saveFailure%', repeated, 0)) /
        sum(if(actions like '%saveAttempt%', repeated, 0)) as 'total',
        sum(if(actions like '%saveAttempt%saveFailure%' and failures like '%userBadToken%', repeated, 0)) /
        sum(if(actions like '%saveAttempt%', repeated, 0)) as 'bad-token',
        sum(if(actions like '%saveAttempt%saveFailure%' and failures like '%userNewUser%', repeated, 0)) /
        sum(if(actions like '%saveAttempt%', repeated, 0)) as 'new-user',
        sum(if(actions like '%saveAttempt%saveFailure%' and failures like '%extensionAbuseFilter%', repeated, 0)) /
        sum(if(actions like '%saveAttempt%', repeated, 0)) as 'abuse-filter',
        sum(if(actions like '%saveAttempt%saveFailure%' and failures like '%extensionCaptcha%', repeated, 0)) /
        sum(if(actions like '%saveAttempt%', repeated, 0)) as 'captcha',
        sum(if(actions like '%saveAttempt%saveFailure%' and failures like '%extensionSpamBlacklist%', repeated, 0)) /
        sum(if(actions like '%saveAttempt%', repeated, 0)) as 'spam-blacklist',
        sum(if(actions like '%saveAttempt%saveFailure%' and failures like '%responseEmpty%', repeated, 0)) /
        sum(if(actions like '%saveAttempt%', repeated, 0)) as 'response-empty',
        sum(if(actions like '%saveAttempt%saveFailure%' and failures like '%responseUnknown%', repeated, 0)) /
        sum(if(actions like '%saveAttempt%', repeated, 0)) as 'response-unknown',
        sum(if(actions like '%saveAttempt%saveFailure%' and failures like '%editPageDeleted%', repeated, 0)) /
        sum(if(actions like '%saveAttempt%', repeated, 0)) as 'page-deleted',
        sum(if(actions like '%saveAttempt%saveFailure%' and failures like '%editConflict%', repeated, 0)) /
        sum(if(actions like '%saveAttempt%', repeated, 0)) as 'conflict'
from (
   select
      day,
      actions,
      failures,
      count(*) as repeated
   from (
      select
         date(timestamp) as day,
         group_concat(event_action order by timestamp, action_order.ord separator '-') as actions,
         group_concat(`event_action.saveFailure.type` order by timestamp separator '-') as failures
      from (
         select
            timestamp,
            event_action,
            `event_action.saveFailure.type`,
            clientIp,
            event_editingSessionId
         from Edit_11448630
         where
            event_editor = 'visualeditor' and
            ('all' = 'all' or wiki = 'all') and
            timestamp >= '20151204000000' and
            timestamp < '20151205000000'

         # Add in events using the new schema. To be removed once everything
         # is switched over.
         union all

         select
            timestamp,
            event_action,
            `event_action.saveFailure.type`,
            clientIp,
            event_editingSessionId
         from Edit_13457736
         where
            event_editor = 'visualeditor' and
            ('all' = 'all' or wiki = 'all') and
            timestamp >= '20151204000000' and
            timestamp < '20151205000000'
         ) raw_events

         inner join
            (select 'init' as action, 0 as ord
                union all
             select 'ready' as action, 1 as ord
                union all
             select 'saveIntent' as action, 2 as ord
                union all
             select 'saveAttempt' as action, 3 as ord
                union all
             select 'saveFailure' as action, 4 as ord
                union all
             select 'saveSuccess' as action, 5 as ord
                union all
             select 'abort' as action, 6 as ord
            ) action_order
         on action = event_action

         # client side, the ip is set but the editingSessionId is bad
         # due to a bad library in Safari, it creates duplicates
         # server side, the ip is not set, but editingSessionId is good
         group by if(
           if( 'visualeditor' = 'wikitext', 0, 1 ),
           concat( coalesce( clientIp, '' ), event_editingSessionId ),
           event_editingSessionId
        )

         # this purposefully ignores sessions that start before "from_timestamp",
         # as well as some other sessions that legitimately do not have an "init"
         # as well as sessions that have an "init" after "ready"
         having actions like 'init%'
   ) sessions
   group by day, actions, failures
) sessions_by_occurrences