How can I obtain a set of results such that each row's date is x numer of days after the previous one?

Question

Lets say I have a table of purchases that looks like this:

Purchases
---------
Item_ID
Purchase_Date
Customer_ID

How would I be able to obtain the set of purchases, starting from the first purchase, for each customer that are at least X days after the last purcahse? For example, if we have the data below, and x=10:

Item_ID    PurchaseDate    Customer_ID    
123        07/29/23        1000
123        08/04/23        1000
123        08/16/23        1000
563        07/03/23        7785
563        07/05/23        7785
788        08/17/23        2489

The query should return:

Item_ID    PurchaseDate    Customer_ID    
123        07/29/23        1000
123        08/11/23        1000
563        07/03/23        7785
788        08/17/23        2489

So, for customer 1000, the first purchase counts, because its a new purchase. The second purchase is less than 10 days from the last purchase so it's discarded. The third purchase for this customer counts because it is more than or equal to 10 days after the last purchase. For customer 7785, only the first counts because the second purcahse is less than 10 days after the last purcahse, and finally for customer 2489, it only has one new purchase so that just counts as the new purchase.(note the first purchase always counts)

I was thinking along the lines of using the lag function

SELECT
    t.Item_ID,
    t.PurchaseDate,
    t.Customer_ID
FROM (
    SELECT
        p.Item_ID,
        p.PurchaseDate,
        p.Customer_ID,        
        LAG OVER(PARTITION BY Customer_ID ORDER BY PurchaseDate) next_purchase
        ROW_NUMBER() OVER(PARTITION BY Customer_ID ORDER BY PurchaseDate) rownum


        
    FROM
        PurchaseTable p
) t
WHERE
    DATEDIFF(DAY, t.PurchaseDate, t.next) >= 10) or rownum=1
       

EDIT: Apologies, I had the description incorrect. I changed the question and example to the correct details.

Answer 1

I have taken your code and polished it a bit - according to your description, it gives results as requested:

   CREATE TABLE Purchases (
Item_ID int, 
purchase_date datetime, 
customer_id int
);

INSERT INTO Purchases
VALUES 
(123,'2023-07-29',1000),
(123,'2023-08-04',1000),
(123,'2023-08-16',1000),
(563,'2023-07-03',7785),
(563,'2023-07-05',7785),
(788,'2023-08-17',2489)

DECLARE @threshold int = -10;   
SELECT
    t.Item_ID,
    t.purchase_date,
    t.Customer_ID
FROM (
    SELECT
        p.Item_ID,
        p.purchase_date,
        p.Customer_ID,        
        LAG(purchase_date) OVER(PARTITION BY Customer_ID ORDER BY purchase_date) next_purchase,
        ROW_NUMBER() OVER(PARTITION BY Customer_ID ORDER BY purchase_date) rownum,
        DATEDIFF(DAY,purchase_date,LAG(purchase_date) OVER(PARTITION BY Customer_ID ORDER BY purchase_date) ) AS purchases_difference
      
    FROM
        Purchases p
) t
WHERE
    (purchases_difference IS NULL OR purchases_difference < @threshold) /* or rownum=1 */
       

The biggest difference to your code is, that LAG gives you negative value (it checks the previous row and subtracts later date from an earlier date - hence negative value. Therefore, I have declared @threshold as -10 (days) and showing everything that's difference -10 days or more (i.e. -11, -12, ...). I have also deliberately commented out the rownum = 1, because that's redundant to purchases_difference IS NULL (it'll be null for every first (or only) row in the group - hence it qualifies the row to be shown).