SQL: Rolling Count over a Time Range

Question

I need to find number of active Customers within last 3 months over a loop. So I go into my Transaction table, and find out the Customers who bought something over a range of 3 months. But I want to achieve it using analytical function.

    select add_months(dat.date,-3) as dateFrom 
           dat.date as dateTo,
           (select count(distinct customerId)
            from Trx_Table
            where Trx_date between add_months(dat.date, -3) and sysdate
           ) as cnt 
    from dim_date dat
    where dat.date between date '2017-01-01' and sysdate;

output is something like this:

dateFrom         dateTo       cnt
20160930         20170101     10
20161001         20170102     12
20161002         20170103     14

This shows that on 20170101, we had 10 customers active between 20160930 and 20170101 (last 3 months)...and so on.

Answer 1

IMHO, analytic functions are not the right use here, since a) it looks like you're after the counts per period, not displaying all rows and showing the rolling count of customers and b) your trx_table may not contain rows for all dates.

Instead, I'd do it like this:

SELECT   add_months(dd.dt, -3) date_from,
         dd.dt date_to,
         COUNT(DISTINCT customerid) cnt
FROM     dim_date dd
         INNER JOIN trx_table tt ON tt.trx_date BETWEEN add_months(dd.dt, -3) AND dd.dt)
GROUP BY add_months(dd.dt, -3),
         dd.dt;