depends on the DB engine, i know oracle handles views very differently, so you may see multiple views chained for performance reasons or to leverage the oracle specific view behavior, which i think is pretty neat

You're probably referring to "materialized views" in Oracle, where each view is indexable and behaves like a table. SQL Server has a somewhat similar concept with putting indexes on views, but I believe it is only available in SQL Server Enterprise Edition (not Standard).

not exactly, in oracle the views/cte are processed in the order they written (can force optimized order), don't need to refresh views based on dependencies, the neat part is you can alter a view's results with a cte by overriding tables within the view. it's just a different approach to views than some other db engines, so you can see views leveraged differently that may be strange coming from different db engines

SQL Server's standard has its version of materialized views (indexed views) in standard, but you have to force queries to use the indexes with a hint - enterprise automatically uses the indexes on them