Count events on "user based" sliding window So, if I wanted to know how many events a certain user bought in certain timeframe windows. I will use 7 for the example, but the query should accept this number of days as a parameter, which will be clear in the code examples.

I could fix on the min(sale_date) of my data and calculate the windows form there, counting each event:
http://sqlfiddle.com/#!4/7cd69/6

but that would not be a sliding window. However if I fix on the max(sale_date), as this date increases the windows are brought along:
http://sqlfiddle.com/#!4/7cd69/7

In these examples, I am using the following mechanism to obtain the time windows:

SELECT DISTINCT
ma - (level - mod(level, 7)) - 7 + 1 dt_lim_bot, 
ma - (level - mod(level, 7)) dt_lim_up,
level - mod(level, 7) slc_id
FROM (select max(dt) ma, min(dt) mi from sales)
CONNECT BY LEVEL &lt;= ma - mi order by 1


(as mentioned, the 7s here could be replaced by any number of days)

Mind the detail: select max(dt) ma, min(dt) mi from sales. Max and min are found regardless if these dates are relevantes for all of my users, which wouldn't be true in a example like: http://sqlfiddle.com/#!4/74f1b/4

I know it will probably not change the result of my initial examples and the problem is probably solved already, though I was curious.

If I try to use select max(dt) ma, min(dt) mi, usr from sales group by usr instead (http://sqlfiddle.com/#!4/74f1b/3), it breaks (infinite loop?). So I figured I should change my connect by too, but couldn't come up with a quick solution.

What would be the correct way to generate these time slices, per user basis?

I am working on Oracle 11r2
Here is another option.  It still doesn't use your CONNECT BY (probably a good thing), but does have fewer table scans.  For the SQL Fiddle I used Phil's modified data.

SELECT usr, usrmax - (slice-1)*7 WindowEnd, count(*) FROM 
(
  SELECT usr, max(dt) OVER (PARTITION BY Usr) usrmax
     , TRUNC((max(dt) OVER (PARTITION BY Usr) - dt)/7) + 1 Slice
  FROM Sales s
) a
GROUP BY usr, usrmax, slice
ORDER BY usr, WindowEnd;


+1 For an excellent and interesting question.