Normalize dates to periods starting at same origin

I’m attempting to track the evolution of customer spending over time. Essentially, I need to identify the first instance of customer purchase, assign it a rank of 1, and assign all of their purchases within that calendar month the same rank for later aggregation. All purchases within the second month for a given customer would be assigned rank 2.

I want to have a customer who signed up in January, and another who signed up in March both designated by a column signaling “first month of shopping.”

Thank you!

Answer

You can use dense_rank(). Since this is a window function, you have to specify your desired window. In this function, we will need to use “partition by” (you can think of it as group by for the given window), and order by because it is required for dense_rank() and enables you to have same number for same month. We will partition by customer and order by year and month. This makes dense_rank() return the same value for the same person for the same month because it doesn’t know which one comes first. This is the reason for using dense_rank() instead of rank().

The code below gives the desired outcome:

SELECT
    Customer
    , [Transaction Amount]
    , dense_rank() over(partition by Customer order by datepart(yy, [Transaction Date]), datepart(yy, [Transaction Date])) as Period
FROM Table