SQL server - count distinct over function or row_numer with rows window function

Question

I am currently trying to get a distinct count for customers over a 90 day rolling period. I have got the amount using sum amount and over partition. However, when I do this with count distinct, SQL doesn't have functionality.

I have attempted to use row_number() with the over partition and use rows current row and 90 preceding but this also isn't available.

Would greatly appreciate any suggested work around to resolve this problem.

I have attempted to solve the problem using 2 approaches, both which have failed based on the limitations outlined above.

Approach 1

select date 
       ,count(distinct(customer_id)) over partition () order by date rows current row and 89 preceding as cust_count_distinct

from table

Approach 2

select date 
       ,customer_id
       ,row_number() over partition (customer_id) order by date rows current row and 89 preceding as rn
from table

-- was then going to filter for rn = '1' but the rows functionality not possible with ranking function windows.

Possible duplicate of Partition Function COUNT() OVER possible using DISTINCT — GSerg
Hello egnkc, and welcome to Stack Overflow :). Can you edit your question and add your SQL statement please? — TT.
To help you, we need you to edit your question and add theses informations : Your current query - The table(s) structure(s) (the CREATE TABLE scripts are really welcome) - Some sample datas - your expected output - the current output — Cid

Gordon Linoff Gordon Linoff · Accepted Answer · 2019-02-12T13:42:56

The simplest method is a correlated subquery of some sort:

select d.date, c.nt
from (select distinct date from t) d cross apply
     (select count(distinct customerid) as cnt
      from t t2
      where t2.date >= dateadd(day, -89, d.date) and
            t2.date <= d.date
     ) c;

This is not particularly efficient (i.e. a killer) on even a medium data set. But it might serve your needs.

You can restrict the dates being returned to test to see if it works.

SQL server - count distinct over function or row_numer with rows window function

1 Answers