The main issue is that the intervals in your second table aren't packed.  Here is an article that discusses Packing Intervals.

The approach I took was to pack...

Here are two options: one using SUBSTRING, the other using STUFF.  You may have to play around with the data lengths.  You can also use strings instead of binary for...

This will happen if the ORDER BY clause in your OVER() clause is non-deterministic.  That is, you have two or more rows that have the same value for the partition/order...

Reported as spam.

You were asked to supply the sample data as INSERT statements, not as a spreadsheet.  Many people hesitate to open files from the Internet, because of the potential for harmful...

You've been around long enough to know how to post sample data and expected results.  Also, one row isn't sufficient.  Adding even one more row to the data creates an...

This uses a windowed function with a frame of 5 rows (2 preceding and 2 following [plus the current row]) and evaluates to "true" (1) if any record in...

You can fix this by changing the ORDER BY in your FIRST_VALUE calculation.
Your first order field should be whether there is a name or not, and then by the...

SQL Server does not guarantee an order unless you specify an order.  Which field(s) are you using to specify your order?

Drew