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

"nickli" <ningli2000 (AT) hotmail (DOT) com> a écrit dans le message de news: 1134517017.107449.295790 (AT) g14g20...oglegroups.com...
What should be the result if there was a visit at 11/08/2005 or 11/09/2005?

Regards
Michel Cadot

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



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

Michel Cadot wrote:

Assuming the base date is the first date for each user
(else you have to change the query in with clause):

SQL> select user_id, purchase_date from t order by user_id, purchase_date;
USER_ID PURCHASE_D
---------- ----------
1 11/01/2005
1 11/03/2005
1 11/08/2005
1 11/09/2005
1 11/11/2005
1 12/01/2005
1 12/05/2005
1 12/18/2005

8 rows selected.

SQL> with min_date as ( select user_id, min(purchase_date) min_date
2 from t
3 group by user_id )
4 select user_id, count(*)
5 from ( select t.user_id, min(t.purchase_date)
6 from t, min_date
7 where min_date.user_id = t.user_id
8 group by t.user_id, trunc((t.purchase_date-min_date.min_date)/7) )
9 group by user_id
10 /
USER_ID COUNT(*)
---------- ----------
1 4

1 row selected.

Regards
Michel Cadot

Thanks for your prompt reply. I tried your solution on the following
dates:

10/5/1996, 10/6/1996, 10/10/1996

The results I got is 2 visits, which should be 1 visit instead,
since 10/5/1996 to 10/10/1996 falls within one rolling 7 day period.

Thanks.

Nick

"nickli" <ningli2000 (AT) hotmail (DOT) com> a écrit dans le message de news: 1134672622.703021.221380 (AT) g14g20...oglegroups.com...
I got 1:

SQL> select user_id, purchase_date from t order by user_id, purchase_date;
USER_ID PURCHASE_D
---------- ----------
1 11/01/2005
1 11/03/2005
1 11/08/2005
1 11/09/2005
1 11/11/2005
1 12/01/2005
1 12/05/2005
1 12/18/2005
2 10/05/1996
2 10/06/1996
2 10/10/1996

11 rows selected.

SQL> with min_date as ( select user_id, min(purchase_date) min_date
2 from t
3 group by user_id )
4 select user_id, count(*)
5 from ( select t.user_id, min(t.purchase_date)
6 from t, min_date
7 where min_date.user_id = t.user_id
8 group by t.user_id, trunc((t.purchase_date-min_date.min_date)/7) )
9 group by user_id
10 /
USER_ID COUNT(*)
---------- ----------
1 4
2 1

2 rows selected.

Actually, it depends of the first day you took.

Regards
Michel Cadot

On 2005-12-13, nickli <ningli2000 (AT) hotmail (DOT) com> wrote:
I have written about a similar problem here:
http://www.adp-gmbh.ch/blog/2005/november/28.html

hth,
Rene


--
Rene Nyffenegger
http://www.adp-gmbh.ch/

Hi,

Thanks for your reply. As you said, the solution depends on the
first day period. I couldn't get the correct answer since there are 2
other dates in my test table: 5/15/1994 and 3/21/1995, in addition to
the dates 10/5/1996, 10/6/1996, 10/10/1996. With these dates added, the
answer will not be correct with the solution. My question is: how can
we calcualate the visits given any time period, no matter if it is in
the same year or not. I have tried to create a solution based on your
period and so far I couldn't figure one out. Any suggestions will be
highly appreciated.

Thanks.

Nick Li

Thanks for your reply. I checked your link and it is very
interesting to see how you use analytic functions to solve the periodos
of activity problem. I have tried to work out a solution based on the
method you used but so far I have not been successful. The difficulty
of the problem is that the rows are not adjacent, so it is hard to use
lag function calculate a moving target for the 7 day rolling period. I
would appreciate your advice on the problem.

Thanks.

Nick Li