MySQL: Count continuous months

Question

I'm trying to count the occurrences when a user perform something in consecutive months. Ex.

| Person     | Datetime              | 
| person_01  | '2015-01-02 10:40:15' | 
| person_01  | '2015-02-02 10:40:15' | 
| person_01  | '2015-07-05 10:40:15' | 
| person_02  | '2015-01-02 10:40:15' | 
| person_02  | '2015-04-03 10:40:15' | 
| person_02  | '2015-07-09 10:40:15' | 

Result:

| Created    |  Consecutive  |
| person_01  |  1            |
| person_02  |  0            |

(I was trying to start by group by year(datetime), month(datetime), but would like to see some examples of solutions for this if possible.)

Any recommendations or examples to help me out?

Answer 1

You can do this by combination of rownumber generation and timestampdiff

SQL Fiddle Demo

SET @row_number1:=0; 

SET @row_number2:=0; 

SELECT T1.person, 
       ,SUM(CASE WHEN TIMESTAMPDIFF(MONTH,T1.ddatetime,T2.ddatetime)=1 THEN 1 ELSE 0 END) CNT CNT 
FROM   (SELECT @row_number1 := @row_number1 + 1 AS row_number, 
               person, 
               ddatetime 
        FROM   datedata 
        ORDER  BY person, 
                  ddatetime) T1 
       INNER JOIN 
       (SELECT @row_number2 := @row_number2 + 1 AS row_number, 
                          person, 
                          ddatetime 
                   FROM   datedata 
                   ORDER  BY person, 
                             ddatetime) T2 
               ON T1.row_number + 1 = T2.row_number 
                  AND T1.person = T2.person 
GROUP  BY T1.person