Is there a workaround for doing a COUNT(DISTINCT) on a window function with a window frame? This is the sample code I’d like to make:
COUNT(DISTINCT customer_id) OVER(Partition By Site, Date ORDER BY Date ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
So my goal is to distinct count customer ids, grouping it by Site & Date (truncated to month) over the past 12 months.
Here is the sample data I have below:
| Site | Date | CustomerID |
|---|---|---|
| Mall A | 12/01/2018 | 948376 |
| Mall A | 09/01/2017 | 948376 |
| Mall A | 02/01/2017 | 498374 |
| Mall B | 05/01/2015 | 394765 |
| Mall B | 12/01/2015 | 293756 |
| Mall B | 11/01/2015 | 345924 |
Here is the desired result:
| Site | Date | n_distinct_customers |
|---|---|---|
| Mall A | 12/01/2018 | 1 |
| Mall A | 09/01/2017 | 2 |
| Mall A | 02/01/2017 | 1 |
| Mall B | 12/01/2015 | 3 |
| Mall B | 11/01/2015 | 2 |
| Mall B | 05/01/2015 | 1 |
1