Write Query With 3 Conditions

Question

I have a CustomerPurchases Table which stores data of each customer's purchases.

I want to retrieve customers who have had at least 2 purchases in less than 4 days apart, with at least 100000 unit amount.

In Oracle, maybe we can handle the condition of the query with below query, but i want a query to write in sql server:

select * from 
(Select distinct  CustomerId,
Sum(PurchaseAmount) over (Partition By CustomerId Order By PrchaseDate  Range Between Interval  4 Day Preceding and Current Row ) as PurchaseAmount,
Count(*) over (Partition By CustomerId Order By PrchaseDate  Range Between Interval  4 Day Preceding and Current Row ) as PurchaseCount
From Customer Purchases) a
Where PurchaseAmount>=100000 and PuchaseCount>=2

For example, considering the table below it should only show CustomerId 3 and 4:

Id	CustomerId	Purchasedate	PurchaseAmount	PurchaseTime
1	1	2021-10-01	500000	10:10:10
2	2	2021_11_01	100000	10:10:10
3	3	2022_03_05	100	10:10:10
4	3	2022_03_07	100000	10:10:10
5	2	2022_03_07	100000	10:10:10
6	4	2022_03_07	100	10:10:10
7	4	2022_03_07	400000	11:10:10

Answer 1

I think the following should work: Per purchase get all other purchases in the four-days range for the same customer. Count these purchases and add up the purchase amount and only keep purchases matching your criteria. Then select distinct customers from the matching purchases.

select distinct p1.customerid
from customer_purchases p1
join customer_purchases p2 
  on  p2.id <> p1.id
  and p2.customerid = p1.customerid
  and p2.prchasedate >= p1.prchasedate
  and p2.prchasedate < date_add(day, 4, p1.prchasedate)
group by p1.customerid, p1.id
having count(*) >= 2 and sum(purchaseamount) >= 100000;