12M Rolling Active Customers

Question

I have a order table with order_date and customer_id fields and for each date in 2022 and beyond I want to compute the 12 month rolling distinct active customers using SQL.

I've tried

SELECT
  order_date,
  COUNT(DISTINCT customer_id) OVER (ORDER BY order_date RANGE INTERVAL '360' day PRECEDING) AS customers_12m
  -- using a window function to create a 360d frame to count distinct customers during that time period
FROM
  orders
WHERE
  order_date >= '2021-01-01'
GROUP BY
  order_date
HAVING
  order_date >= '2022-01-01'

Somehow I having a problem with different errors (distinct not supported, range not supported, etc.) based on different DBMS platforms (Redshift, Exasol, Trino (Presto)). Can someone please guide me if this is possible to achieve using just SQL?

Sample Input

order_date	customer_id
2023-01-01	X
2023-01-01	Y
2023-01-02	X
2023-01-03	X
2023-01-04	Z
2023-01-05	Z

Sample Output

Instead of 360 days, assume 1 day here

order_date	customers_1d
2023-01-01	2 (X and Y)
2023-01-02	2 (X and Y)
2023-01-03	1 (X only)
2023-01-04	2 (X and Z)
2023-01-05	1 (Z only)

Answer 1

Wouldn’t this give you what you want?

SELECT
  order_date,
  COUNT(DISTINCT customer_id)
FROM
  orders
WHERE
  order_date >= '2021-01-01'
GROUP BY
  order_date