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):
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.
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.