SQL Query to set flag based on Start Date and Previous Close Date

Question

I've tried searching this up, but nothing really matched with what I was looking for, so any help is appreciated!

And what I want is to have an Expression or flag which should return true if the number of days between previous closed date and current Start date is less than 10 for the TASK ID.

For Example The above table should looks like

Note: I only have read-only access, so 'update queries' and 'create new table' queries are not applicable for my case.

Answer 1

In SQL Server, you can use lag():

select t.*,
       (case when lag(close_date) over (partition by task_id) < dateadd(day, -10, close_date)
             then 1 else 0
        end) as flag
from t;