Count events on "user based" sliding window

Question

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 <= 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

Answer 1

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;

⁺¹ For an excellent and interesting question.

Answer 2

I've done it using a conversion into a Julian date, plus a hacky division, truncate, then multiply.

Source data to show the upper window is correct, and different for each user:

with data as
    (
      select usr,TO_NUMBER(TO_CHAR(max(dt), 'J')) as maxwindow
      from sales
      group by usr
    )
select dt, TO_DATE((trunc((TO_NUMBER(TO_CHAR(dt, 'J'))-maxwindow)/7)*7)+maxwindow,'J') as grouping_date_upper_window, sales.usr,cmp
from data,sales
where data.usr=sales.usr

Finished query:

with data as
    (
      select usr,TO_NUMBER(TO_CHAR(max(dt), 'J')) as maxwindow
      from sales
      group by usr
    )
select usr,GROUPING_DATE_UPPER_WINDOW,count(*)
from (
select sales.dt, TO_DATE((trunc((TO_NUMBER(TO_CHAR(dt, 'J'))-maxwindow)/7)*7)+maxwindow,'J') as grouping_date_upper_window, sales.usr,sales.cmp
from data,sales
where data.usr=sales.usr
  )
group by usr, GROUPING_DATE_UPPER_WINDOW
order by 1,2;

Just plug the window size into where the two "7" values currently are.

SQL Fiddle here - new fiddle with slightly altered data so you can see that the windows are different for each usr.