How to set in SQL a Flag based on days range

Question

Sample table (customer) have the following data,

RecID	createdDate	UserID	ROWNUMBER	toCount
1	10-25-2022	User01	1	true
2	10-14-2022	User01	2	true
3	01-25-2020	User01	3	true
4	10-19-2022	User02	1	true

As per below query, will get the similar customer with rowNumber(). Think the problem is the the comparison of data set with createdDate.

select
    RecID, createdDate, UserID,
    row_number() over (partition by UserID order by UserID) as "ROWNUMBER",
    toCount
from (
    select
       *,
       (case when datediff(day, lag(createdDate,50,createdDate) over (partition by UserID order by UserID), createdDate) <= 1 
             then 'true'
             else 'false' 
        end) as toCount
    from customer
) t

The problem: All users should receive a flag (count), who had not registered in the last 50 days. like this:

RecID	createdDate	UserID	ROWNUMBER	toCount
1	10-25-2022	User01	1	false
2	10-14-2022	User01	2	true
3	01-25-2020	User01	3	true
4	10-19-2022	User02	1	true

Answer 1

First, here's a query that I think gives you what you're looking for (let me know):

select RecId
     , createdDate
     , UserId
     , row_number() over (partition by UserId order by createdDate desc) as ROWNUMBER
     , case
          when datediff(day,lag(createdDate) over (partition by UserId order by createdDate),createdDate) <= 50 then 'false'
          else 'true'
       end as toCount
  from customer
order by RecId;

A couple of observations:

row_number() over (partition by UserID order by UserID) as "ROWNUMBER" UserID is not distinct and doesn't make a good candidate for the order by in this row_number function. It's good for partition, not for order by.

lag(createdDate,50,createdDate) That 50 in there is an offset, so you're asking to skip fifty rows, not 50 days.