Number of Customer Purchases in Their First Month

I have a list of customer orders. I can easily calculate the month and year of first purchase for each customer (e.g. customer 1 had their first purchase in Sept 2021, customer 2 had their first purchase in Oct 2021, etc.). What I want to add is an additional column that counts the number of purchases a customer made in their first month.

Existing data table (Orders):

OrderId	CustomerId	OrderDate
1	1	9/15/2021
2	1	10/15/2021
3	1	11/1/2021
4	2	10/1/2021
5	2	10/6/2021
6	2	10/7/2021
7	2	11/9/2021
8	3	11/15/2021

Desired output:

CustomerId	FirstOrderMonth	FirstOrderYear	FirstMonthPurchaseCount
1	9	2021	1
2	10	2021	3
3	11	2021	1

I was thinking something like this for the first three columns:

SELECT o.CustomerId,
MONTH(MIN(o.OrderDate)) as FirstOrderMonth,
YEAR(MIN(o.OrderDate)) as FirstOrderYear
FROM Orders o
GROUP BY o.CustomerId

I am not sure how to approach the final column and was hoping for some help.

Answer

Aggregate by the customer’s id, the year and the month of the order and use window functions to get the year and month of the 1st order and the count of that 1st month:

SELECT DISTINCT CustomerId,
       FIRST_VALUE(MONTH(OrderDate)) OVER (PARTITION BY CustomerId ORDER BY YEAR(OrderDate), MONTH(OrderDate)) FirstOrderMonth,
       MIN(YEAR(OrderDate)) OVER (PARTITION BY CustomerId) FirstOrderYear,
       FIRST_VALUE(COUNT(*)) OVER (PARTITION BY CustomerId ORDER BY YEAR(OrderDate), MONTH(OrderDate)) FirstMonthPurchaseCount
FROM Orders
GROUP BY CustomerId, YEAR(OrderDate), MONTH(OrderDate);

See the demo.