Write Query That Consider Date Interval

Question

I have a table that contains Transactions of Customers. I should Find Customers That had have at least 2 transaction with amount>20000 in Three consecutive days each month. For example , Today is 2022/03/12 , I should Gather Data Of Transactions From 2022/02/13 To 2022/03/12, Then check These Data and See If a Customer had at least 2 Transaction With Amount>=20000 in Three consecutive days.

For Example, Consider Below Table:

Id	CustomerId	Transactiondate	Amount
1	1	2022-01-01	50000
2	2	2022_02_01	20000
3	3	2022_03_05	30000
4	3	2022_03_07	40000
5	2	2022_03_07	20000
6	4	2022_03_07	30000
7	4	2022_03_07	30000

The Out Put Should be : CustomerId =3 and CustomerId=4

I write query that Find Customer For Special day , but i don't know how to find these customers in one month with out using loop. the query for special day is:

With cte (select customerid, amount, TransactionDate,Dateadd(day,-2,TransactionDate) as PrevDate
From Transaction 
Where TransactionDate=2022-03-12)
Select CustomerId,Count(*)
From Cte 
Where 
TransactionDate>=Prevdate and TransactionDate<=TransactionDate
And Amount>=20000
Group By CustomerId
Having count(*)>=2

Answer 1

Hi there are many options how to achieve this.
I think that easies (from perfomance maybe not) is using LAG function:

WITH lagged_days AS (
SELECT
     ISNULL(LAG(Transactiondate) OVER(PARTITION BY CustomerID ORDER BY id),
LEAD(Transactiondate) OVER(PARTITION BY CustomerID ORDER BY id)) lagged_dt
    ,*
FROM Transaction
), valid_cust_base as (
SELECT
    * 
FROM lagged_days
WHERE DATEPART(MONTH, lagged) = DATEPART(MONTH, Transactiondate) 
AND datediff(day, Transactiondate, lagged_dt) <= 3
AND Amount >= 20000
)
SELECT
CustomerID
FROM valid_cust_base 
GROUP BY CustomerID
HAVING COUNT(*) >= 2

First I have created lagged TransactionDate over customer (I assume that id is incremental). Then I have Selected only transactions within one month, with amount >= 20000 and where date difference between transaction is less then 4 days. Then just select customers who had more than 1 transaction.

In LAG First value is always missing per Customer missing, but you still need to be able say: 1st and 2nd transaction are within 3 days. Thats why I am replacing first NULL value with LEAD. It doesn't matter if you use:

ISNULL(LAG(Transactiondate) OVER(PARTITION BY CustomerID ORDER BY id),
LEAD(Transactiondate) OVER(PARTITION BY CustomerID ORDER BY id)) lagged_dt

OR

ISNULL(LEAD(Transactiondate) OVER(PARTITION BY CustomerID ORDER BY id),
LAG(Transactiondate) OVER(PARTITION BY CustomerID ORDER BY id)) lagged_dt

The main goal is to have for each transaction closest TransactionDate.