Count distinct per fiscal year and display all dates in query result

DB-Fiddle:

CREATE TABLE customers (
    id int auto_increment primary key,
    order_date DATE,
    customerID VARCHAR(255)
);

INSERT INTO customers
(order_date, customerID
)
VALUES 
("2020-01-15", "Customer_01"),
("2020-02-15", "Customer_01"),
("2020-03-18", "Customer_01"),
("2020-04-22", "Customer_01"),
("2021-01-19", "Customer_01"),

("2020-01-25", "Customer_02"),
("2020-02-26", "Customer_02"),
("2020-11-23", "Customer_02"),
("2021-01-17", "Customer_02"),
("2021-02-20", "Customer_02");

Expected Result:

order_date   |      quantity
             |    (fiscal year)
-----------------------------------------------------------------
2020-01-15   |           1         --> Customer_01 appears the first time between 2019-03 and 2020-02)
2020-01-25   |           1         --> Customer_02 appears the first time between 2019-03 and 2020-02)
2020-02-15   |           0
2020-02-26   |           0
2020-03-18   |           1         --> Customer_01 appears the first time between 2020-03 and 2021-02
2020-04-22   |           0
2020-11-23   |           1         --> Customer_02 appears the first time between 2020-03 and 2021-02
2021-01-17   |           0
2021-01-19   |           0
2021-02-20   |           0

In the above result I want to list all order dates and count the number of customers distinct per fiscal year.
The fiscal year starts two months after the calender year and therefore goes from March to February.
(e.g. from 2020-03 til 2021-02).

For example Customer_01 appears the first time on 2020-03-18 within the fiscal year 2020-03 til 2021-02.
Therefore, this order_date gets assigned 1 to it.
If the customer appears again within the fiscal year the next order_date will get assigned 0 to it.

So far I have used this query:

SELECT
order_date,
SUM(rn = 1) AS quantity
FROM 
  (SELECT 
  order_date, 
  row_number() over(PARTITION BY YEAR(order_date), customerID ORDER BY order_date) rn
  FROM customers
  ) t
GROUP BY 1;

It works perfectly for the calender year.
However, I have no clue how I can apply the two months shift from the fiscal year to it.
Do you have any idea?

Answer

I have updated your query as below:

SELECT
order_date,
SUM(rn = 1) AS quantity
FROM 

  (SELECT 
  order_date, 
  row_number() over(PARTITION BY YEAR(order_date - INTERVAL 2 MONTH), customerID ORDER BY order_date) rn
  FROM customers
  ) t
  
GROUP BY 1;

Advertisement

Answer