SQL Problam

Tools

SQL Problam

Hi All,

We have a requirement wherein Customer who has made purchases of at least $ 10000 in a 6 month period and made atleast a single visit a month in a 6 consecutive month period should be given a rating of A+B+.

and

Customer who has made purchases of at least $ 10000 in a 6 month period but not made atleast a single visit a month in a in a 6 consecutive month period should be given a rating of A+B-.

sample data

CUSTOMER_ID PURCHASE_DATE PURCHASE_AMOUNT

101 02-MAR-07 10000
101 02-APR-07 8000
101 05-MAY-07 10000
105 18-JAN-08 10000
101 18-FEB-08 5000
102 18-MAR-08 20000
103 18-APR-08 3000
102 23-MAY-08 2000
104 02-JUN-08 10000
103 02-JUN-08 30000
101 02-JAN-08 51000

With this kind of data we need to write a query to retrieve only those customers who have data in each of the last six months, like in this example it should be customer number '101'.

Customer '103' should be ignored as it does not have data for last six months CONSECUTIVELY

Thanks & Regards,

Praveen