Number of Customer Purchases in Their First Month

user_9023614 0
Viewed 112

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.

window-functions group-by date sql-server sql
3 Answers

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.

user_104988280 
select   CustomerId
        ,min(month(OrderDate))   as FirstOrderMonth
        ,min(year(OrderDate))    as FirstOrderYear
        ,count(first_month_flag) as FirstMonthPurchaseCount
        
from    (select *
                ,case when month(OrderDate) = month(min(OrderDate) over(partition by CustomerId)) then 1 end as first_month_flag
         from Orders) Orders

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

Fiddle

user_191745700

You may use the RANK() function to identify the first month purchases for each user as the following:

Select D.CustomerId, MONTH(OrderDate) FirstOrderMonth, 
       YEAR(OrderDate) FirstOrderYear, COUNT(*) FirstMonthPurchaseCount
From
(
  Select *, RANK() Over (Partition By CustomerId Order By YEAR(OrderDate), MONTH(OrderDate)) rnk
  From table_name
) D
Where D.rnk = 1
Group By  D.CustomerId, MONTH(OrderDate), YEAR(OrderDate)

See a demo.

If you want to find second, third ... month purchases, you may use the DENSE_RANK() function instead of RANK() and change the value in the where clause to the required month order.

user_127059120
Related