This is rather old and there are better ways of doing most of these things now. For instance the counting example would usually be much more efficient performed using the ROW_NUMBER() window function instead of a Cartesian product. When you can remove a cross product from your process it is almost always beneficial to do so. That will often involve introducing a CTE⁰¹ which might put off some beginners²³, but it shouldn't as this sort of example is pretty simple (you aren't worrying about any recursive case).
----
[0] Because you want the ordinal of the row in the input table/view, not your output.
[1] You could also use a sub-query, in most cases a good query planner will see the equivalence and do the same thing for either. The CTE option is easier to read and maintain IMO.
[2] In databases, like sports, CTEs can result in headaches!
[3] Or veterans of postgres, where until a few years ago CTEs were an optimisation gate, blocking predicate push-down and making some filtered queries a lot more expensive (though often no more so than the naive Cartesian product method).