Mysql consecutive days user wise

Question

Table 'present'

+--------+------------+
| emp_id | pre_date   |
+-----------+---------+
| 002    | 2019-04-01 |
| 001    | 2019-04-21 |
| 001    | 2019-04-15 |
| 001    | 2019-04-19 |
| 002    | 2019-04-15 |
| 002    | 2019-04-16 |
| 002    | 2019-04-10 |
| 001    | 2019-04-10 |
| 001    | 2019-04-20 |
| 002    | 2019-04-19 |
| 001    | 2019-04-01 |
| 001    | 2019-04-30 |
| 002    | 2019-04-21 |
| 002    | 2019-04-24 |
| 001    | 2019-04-16 |
| 002    | 2019-04-20 |
| 001    | 2019-04-24 |
+-----------+---------+

I want result

+--------+------------+
| emp_id | count      |
+-----------+---------+
| 001    |3           |
+-----------+---------+

Because Emp 001 present 2019-04-19, 2019-04-20, 2019-04-21 that 3 consecutive days

Answer 1

If I get it right, you would like to find the longest consecutive interval per user. Here's one attempt, the idea is to find the start and stop for each interval. The start is defined as a date where it does not exists a row for the day before that, and similarly for the stop for the consecutive date

select emp_id, max(datediff(stop, start)) + 1
from (                            
    select lb.emp_id, lb.pre_date as start, min(ub.pre_date) as stop
    from (
        select emp_id, pre_date
        from present p1
        where not exists (
          select 1 
          from present p2
          where p2.pre_date = date_sub(p1.pre_date, interval 1 day)
            and p2.emp_id = p1.emp_id
        )
    ) as lb
    join (
        select emp_id, pre_date
        from present p3
        where not exists (
          select 1 
          from present p4
          where p4.pre_date = date_add(p3.pre_date, interval 1 day)
            and p4.emp_id = p3.emp_id
        )
    ) as ub
        on lb.emp_id = ub.emp_id
        and ub.pre_date >= lb.pre_date
    group by lb.emp_id, lb.pre_date
) as t
group by emp_id; 

Answer 2

Simplest way to use sub query.

SELECT emp_id,count(*) as count FROM ( SELECT *, count(p.id) FROM present p GROUP BY pre_date , emp_id ) d group by d.emp_id