Q:

select 5 consecutive date's record with group by user

I have the following table structure in which I just want those record which is in 5 consecutive dates and group by uid.
This is my data structure
user_master
uid | name
1   |  A
2   |  B
3   |  C
4   |  D

tbl_referral
refId   |  uid  |   Date
1       |  1    |   01-09-2018
2       |  1    |   02-09-2018
3       |  1    |   03-09-2018
4       |  2    |   01-09-2018
5       |  2    |   02-09-2018
6       |  1    |   04-09-2018
7       |  2    |   05-09-2018
8       |  2    |   06-09-2018
9       |  1    |   05-09-2018
10      |  2    |   06-09-2018

Now I want to fetch only those record from tbl_referral whose date is appearing consecutive 5 times 
required output is
uid | Date
    | 01-09-2018
    | 02-09-2018
1   | 03-09-2018
    | 04-09-2018
    | 05-09-2018

A:

You can join the table with itself and count the number of rows in the 5-day range. If the count is 5, the range has all consecutive dates.
SELECT t1.uid, GROUP_CONCAT(t2.Date) AS dates
FROM tbl_referral AS t1
JOIN tbl_referral AS t2 ON t1.uid = t2.uid AND t2.date BETWEEN t1.date AND DATE_ADD(t1.date, INTERVAL 4 DAY)
GROUP BY t1.uid, t1.date
HAVING COUNT(*) = 5

This query assumes that the datatype of the date column is DATE, not a string. If it's a string, you'll need to use STR_TO_DATE() to convert it to a date as in Nick's answer. But it's best to store dates as DATE rather than strings, so you can make use of indexes (they also take up less space).

