Re: Question about SQL Query

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

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