Find clients who bought more than 1 million units in 3 consecutive months

Question

I want to find the client's code (cCode), who bought more than 1 million units, in three consecutive months.

Here is my data:

gDate        cCode        buy
2014-10-22  15108164    1653410
2014-12-22  15108164    1653410
2014-10-22  16100286    1255720
2014-11-22  16100286    1739850
2014-12-22  16100286    1255720
2014-09-22  12400573    1336000
2014-10-22  12400573    1336000
2014-12-22  12400573    1336000
2014-12-22  12600125    3592800
2014-10-22  12600125    1266200

where cCode is the client's code, or ID.

Result must:

------------------------------------------------------------
cCode       2014-09-*   2014-10-*   2014-11-*   2014-12-*   |
------------------------------------------------------------
15108164        -           Yes         -           Yes     |
16100286        -           Yes         Yes         Yes     |  <- OK
12400573        Yes         Yes         -           Yes     |
12600125        -           Yes         -           Yes     |
------------------------------------------------------------
Answer is(cCode) : 16100286

I update function

DELIMITER $$

CREATE FUNCTION `milad`(D date,I int ,mMonth int ,minBuy int) RETURNS tinyint(1)
BEGIN
DECLARE result TINYINT Default 1 ;
DECLARE m_counter INT Default 0 ;

myloop: WHILE m_counter < mMonth DO
 if((SELECT count(cCode) as a FROM milad WHERE
    year(gDate) = year(DATE_ADD(D,INTERVAL m_counter month))
   and month(gDate) = month(DATE_ADD(D,INTERVAL m_counter month))
   and buy>minBuy
   and cCode=I)=0) THEN SET result = 0;
 END IF;
 SET m_counter = m_counter+1;
END WHILE myloop;

IF(result=1)
 THEN RETURN true;
 ELSE RETURN false;
end if;
end

Answer 1

Before the edit

I created a table to test here the create code ( you will have to alter your table and add unique identifier for this to work )

CREATE TABLE `milad` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `cCode` int(10) unsigned NOT NULL,
 `gDate` date NOT NULL,
 `buy` int(10) unsigned NOT NULL,
 PRIMARY KEY (`id`)
)

Then I inserted the date you used and test with 3 days not 3 months

SELECT t2.cCode,sum(t2.buy) AS total 
FROM milad As t1 
INNER JOIN milad AS t2 
ON t1.cCode=t2.cCode 
WHERE t1.gDate<=DATE_ADD(t2.gDate,INTERVAL 3 day) 
group by (t1.id) 
having total>1000000 
ORDER BY t1.cCode , total DESC

Here is the result

+----------+---------+
| cCode    | total   |
+----------+---------+
| 12400573 | 4008000 |
| 12400573 | 2672000 |
| 12400573 | 1336000 |
| 12600125 | 4859000 |
| 12600125 | 3592800 |
| 15108164 | 3306820 |
| 15108164 | 1653410 |
| 16100286 | 4251290 |
| 16100286 | 2995570 |
| 16100286 | 1255720 |
+----------+---------+

---

After the edit

SELECT t1.cCode
FROM milad As t1
INNER JOIN milad AS t2
ON t1.cCode=t2.cCode
and (
  (
    year(t2.gDate) = year(DATE_ADD(t1.gDate,INTERVAL 1 month)) 
    and month(t2.gDate) = month(DATE_ADD(t1.gDate,INTERVAL 1 month))
  )
  or 
  (
    year(t2.gDate) = year(DATE_ADD(t1.gDate,INTERVAL 2 month)) 
    and month(t2.gDate) = month(DATE_ADD(t1.gDate,INTERVAL 2 month))
  )
)
group by t1.cCode,t1.gDate
having count(t1.gDate)=2

The result

+----------+
| cCode    |
+----------+
| 16100286 |
+----------+

---

Another solution using a function

Function code

DELIMITER $$
CREATE FUNCTION milad(D date,I int) RETURNS boolean
BEGIN
if
(SELECT count(cCode) as a FROM milad WHERE
    year(gDate) = year(DATE_ADD(D,INTERVAL 1 month))
    and month(gDate) = month(DATE_ADD(D,INTERVAL 1 month))
    and buy>1000000
    and cCode=I
 )>0
and 
(SELECT count(cCode) as a FROM milad WHERE
    year(gDate) = year(DATE_ADD(D,INTERVAL 2 month))
    and month(gDate) = month(DATE_ADD(D,INTERVAL 2 month))
    and buy>1000000
    and cCode=I
 )>0
THEN RETURN true;
ELSE RETURN false;
end if;
end$$
DELIMITER ;

Then just use this query

SELECT cCode
FROM milad
where milad(gDate, cCode)

The result

+----------+
| cCode    |
+----------+
| 16100286 |
+----------+

This will work if there are two rows in same month for the same person.

Please if someone can do it without a function, write the answer and mention me.