I worked it out in my head, got it right, then ran the query on my system and got a different result: error in query. On my system NAME and Name are two different columns, because the server collation is case sensitive. But I am used to that - many QotD authors never test their questions on case sensitive servers. When making the necessary changes to make the code run, I noticed that two of the COALESCE functions use NULL instead of Name - a fact I had overlooked when working it out in my head (that luckily did not affect the result). Was this intentional??
The case insensitivity of the data was not an issue for me - the "select three" made it obvious that James = JAMES in this question.