Explain an OVER clause

ROW_NUMBER() is a ranking window function, and ranking window functions require a mandatory ORDER BY clause. If you try to write it without the ORDER BY you will get a syntax error.

SELECT ROW_NUMBER() OVER()
FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
-- Msg 4112, Level 15, State 1, Line 1 
-- The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.

The trick with the subquery was discovered by someone who blogged about it, as a performance optimization. SQL Server always performs a sort operation since constants are not allowed for the ORDER BY clause:

SELECT ROW_NUMBER() OVER(ORDER BY NULL)
FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
-- Msg 5309, Level 16, State 1, Line 1
-- Windowed functions, aggregates and NEXT VALUE FOR functions 
-- do not support constants as ORDER BY clause expressions.

And neither are integers which are treated as indices:

SELECT ROW_NUMBER() OVER(ORDER BY 1)
FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
-- Msg 5308, Level 16, State 1, Line 1
-- Windowed functions, aggregates and NEXT VALUE FOR functions 
-- do not support integer indices as ORDER BY clause expressions.

Turns out that due to some glitch in the code, you can circumvent this limitation by using a subquery, which for some reason is allowed, and eliminates the sort operator.

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);

You can use any constant in the sub query, the NULL is probably a reminiscent of the habit of using SELECT NULL within EXISTS predicates, which in the early history of SQL Server had a performance impact as opposed to * or any other column expression, as the optimizer wasn't smart enough to ignore it.

HTH

UPDATE @Erik-Darling commented that you can also circumvent it by using a computed expressions:

You can do SELECT ROW_NUMBER() OVER (ORDER BY 1/0);

The OVER clause sets the ordering (and partitioning if PARTITION BY is included) of the row set before applying the selected Windowing function. As you can use multiple Window functions in a single query, each requires its own partitioning and ordering to ensure the data is returned as desired.

In your example, ROW_NUMBER() is being used to generate a sequential row number for each row in the CTE. SELECT NULL is used because there is no particular order required but an ORDER BY clause is required for a windowing function.

Another way of achieving the same thing would be using an IDENTITY column, however, that has other implications and requires changes to an existing table, or a temp table to be created. The ROW_NUMBER window function in a CTE allows for this identity to be generated on the fly.

To answer your specific question:

Essentially, what exactly is it doing?

It is ordering the rows in E4 'randomly' before applying the ROW_NUMBER() window function to that result set to produce a list of row numbers equal to the number of rows in E4. OVER can be translated as "Fetch me a result set with this ordering and partitioning and apply (OVER) this window function to that result set independent of the ordering in the main SELECT statement."

More info: OVER Clause