Re: Question about SQL Query

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 15 Dec 2005 22:22:15 +0100
Message-ID: <43a1e0e6$0$14553$626a14ce@news.free.fr>

"nickli" <ningli2000_at_hotmail.com> a écrit dans le message de news: 1134672622.703021.221380_at_g14g2000cwa.googlegroups.com...
| 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
|

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 Received on Thu Dec 15 2005 - 15:22:15 CST