Re: Question about SQL Query

Hi,

    I apologize for not making the question clear.

    In your case, purchase dates will be: 11/1/2005, 11/3/2005, 11/08/2005, 11/09/2005, 11/11/2005, 12/1/2005, 12/5/2005, 12/18/2005.

    The first visit includes purchase dates 11/1/2005 and 11/3/2005 as the first rolling 7 day is between 11/1/2005 and 11/7/2005.

    The second visit includes purchase dates 11/08/2005, 11/09/2005 and 11/11/2005, since the second rolling 7 day period is between 11/8 and 11/14.

     There are no purchase dates between 11/11/2005 and 12/1/2005, so the next rolling 7 day period starts on 12/1/2005 and ends in 7 days at 12/7/2005. The purchase dates 12/1/2005 and 12/5/2005 counts as the third visit.

     The last visit includes purchase date 12/18/2005.

     There are a total of 4 visits in this case.

     Thanks.

     Nick Li

11/11/2005 counts as one visit
> | 12/1/2005 and 12/5/2005 count as one visit
> | 12/18/2005 counts as one visit

    I am counting the number of store visits within current calendar year, starting at January 1, 2005.

• For this example,

Michel Cadot wrote:
> "nickli" <ningli2000_at_hotmail.com> a écrit dans le message de news: 1134517017.107449.295790_at_g14g2000cwa.googlegroups.com...
> | Hi,
> |
> | I was able to solve the following problem using PL/SQL. However I
> | would like to know if it can be solved using a SQL query:
> |
> | The table has these columns: user_id, purchase_date
> | I would like to count the number of store visits by a person
> | within a calendar year. Purchase_dates within a rolling 7 day period
> | are counted as one store visit. There can be several or no purchases
> | within these 7 days. Next purchase date can start any time. For
> | example:
> |
> | Purchase_Date: 11/1/2005, 11/3/2005, 11/11/2005, 12/1/2005,
> | 12/5/2005, 12/18/2005
> | Number of Visits = 4
> | 11/1/2005 and 11/3/2005 count as one visit
> | 11/11/2005 counts as one visit
> | 12/1/2005 and 12/5/2005 count as one visit
> | 12/18/2005 counts as one visit
> |
> | Thanks in advance.
> |
> | Nick Li
> |
>
> What should be the result if there was a visit at 11/08/2005 or 11/09/2005?
>
> Regards
> Michel Cadot
Received on Wed Dec 14 2005 - 15:55:59 CST