Not really. In this context they're a hint to the optimizer to evaluate the CTE result store=materialize it somewhere and use that snapshot when the CTE is queried subsequently.

The alternative means the CTE is copy-pasted essentially and them evaluated.

The closest one can get to materialized views in SQLite is probably with CREATE TABLE AS. Drop and recreate.