Here is a comparison of the two methods:

/* Windowed Aggregate (MAX)  */Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads...

I just did a deep dive on that code.  ANSI NULLs are the key there!  Really cool code!  Thanks, again, Drew.  Do you have a link where Itzik provides...

Here is a comparison of the two methods:

/* Windowed Aggregate (MAX)  */
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob...

As a side note, there is no way to implement this using just LAG(), since 2022-07-01 can only be the immediate predecessor of one record and you would need it...

I believe that this gives you your desired results.  It's based on code by Itzik Ben-Gan.  I prefer to use BINARY rather than CHAR except in the case of DATE...

Please don't cross post.  It fragments the replies.  Other thread https://www.sqlservercentral.com/forums/topic/query-help-299#post-4072505

Drew

I believe that this code is faster.  It's based on code by Itzik Ben-Gan.

SELECT ID
     , t.VAL
     , CAST(SUBSTRING(MAX(CAST(ID...

• My first thought is that you shouldn't refactor code just to use a shiny new toy.
• My second thought is that it already uses a CTE (two in fact),...

First, this website is specifically dedicated to Transact SQL.  You might be better off posting to a website specifically for mySQL or to a general SQL website.

Second, stating only that...

Change your point of view.  Instead of looking back to see which customers were active, spread your customers' activities forward.  The following only takes one scan and one logical read.