Select

select.sql

select count( distinct user_id ) as n, d, dt from
(
  select user_id,
         z.zt as d,
         G.dt      
  from 
    messages
  cross join
     ( select distinct DATE_FORMAT( messages.date, '%Y-%m-%d') as zt from messages) z
  inner join
    ( select 7 as dt union all 
      select 6 union all select 5 union all
      select 4 union all select 3 union all
      select 2 union all select 1 union all select 0) G
  on DATE_FORMAT( messages.date, '%Y-%m-%d') between
        DATE_FORMAT( date_add( z.zt, Interval -1 * G.dt DAY) , 
                    '%Y-%m-%d')
     and
        z.zt
  where z.zt <= z.zt
  group by user_id, G.dt, z.zt
  having  count( distinct DATE_FORMAT( messages.date, '%Y-%m-%d') )  = G.dt + 1
) TT
group by d,dt
order by d,dt