fwiw, this is exactly the behavior I would expect. Opening a folder means opening a project, so ⌘N can create a new file in that folder. If I’m not in a folder window, I expect to work with individual files so ⌘N should create a new unsaved Untitled file.
But that’s not how it works. The behavior of Command-N doesn’t change, whether you’re “in a folder” or just viewing a single file. It always opens a new file in the base folder.
I know they can be moved from iCloud, but it’s a lot easier to do that from the File menu than to have to drag and drop from the sidebar, since you have to open the folder where you want to put the file. I think it makes sense that apps that use iCloud Drive by default have a File > Move To menu item.