Number of Customer Purchases in Their First Month-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

select   CustomerId
        ,min(month(OrderDate)) as FirstOrderMonth
        ,min(year(OrderDate))  as FirstOrderYear
        ,count(cnt)            as FirstMonthPurchaseCount
        
from    (select *
                ,case when month(OrderDate) like month(min(OrderDate) 
                      over(partition by CustomerId)) then OrderId end as cnt
         from Orders) Orders

group by CustomerId

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

Fiddle

CodePudding user response：

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.

CodePudding user response：

If it you need orders till 1 month after first order then try this:

With FirstOrders AS (
SELECT o.CustomerId,
MIN(o.OrderDate) MinOrderDate
FROM Orders o
GROUP BY o.CustomerId
), OrderCount AS (
Select Count(1) as [Count],
o.CustomerId
  from Orders o
    inner join FirstOrders fo on fo.CustomerId = o.CustomerId 
Where DateDiff (Month, MinOrderDate, o.OrderDate) = 1
group by o.CustomerId
)

Select * from OrderCount