with source as (
    select * from wherever
  ),
  transformed as (
    ...
  ),
  joined as (
    ...
  ),
  final as (
    ...
  )
  select * from final

Yes, materialization is fine for small hardcoded values in the post, but for most other lookup tables, it's definitely not fine.

For example, you read this article and think great, I'll make a lookup table to map various ids to be linked across tables.

    with ids as (
        select u.user_id, u.token, s.customer_id
        from users u
        join stripe_customer s on s.user_id = u.user_id
    ),
    user_impressions as (
        select i.*, ids.id
        from impressions i join ids using token
    ),
    payments as (
        select p.*, ids.id from payments p
        join ids using customer_id
    ),
    select sum(i.views),
           sum(i.clicks),
           sum(p.amount)
    from payments p
    join user_impressions i using user_id
    where user_id = $1
    group by date;

Nope.. since ids is referenced more than once in this query, in Postgres this causes the ids CTE to be materialized onto disk with no indexes. So not only does it take up lots of extra space on disk to store all the users in your system, to join user_impressions and payments you have to O(N) search across the ids dataset. No indexes on a materialized table.

It would be a lot faster to join to users or stripe_customer or both in a loop with indexes than O(N) search through all the users in your system.

This is particularly dangerous because if you remove the payments part, ids only has one reference, so Postgres doesn't materialize the table, and everything is fast.

This can be fixed by adding WITH foobar AS NOT MATERIALIZED to the CTE syntax. IMO it should be a syntax error to not specify AS MATERIALIZED or AS NOT MATERIALIZED.. The default has too many potential performance problems and folks should have to think about whether they want materialization or not.

In what world is this

  WITH countries (code, name) AS (
     ...>   SELECT \* FROM (VALUES
     ...>     ('us', 'United States'), ('fr', 'France'), ('in', 'India')
     ...>   ) AS codes
     ...> )
     ...> SELECT data.code, name FROM data LEFT JOIN countries ON countries.code = data.code;

  SELECT code,
     ...> CASE code 
     ...>   WHEN 'us' THEN 'United States'
     ...>   WHEN 'fr' THEN 'France'
     ...>   WHEN 'in' THEN 'India'
     ...>  END AS country
     ...> FROM data;

Is there any reason syntactically the SELECT needs to be required? If you use a VALUES table literal as a subselect, you have to give it column names with an AS clause (see https://www.postgresql.org/docs/current/sql-values.html). I can imagine a simpler syntax where you'd do WITH VALUES ... AS table_name (column_1_name, ...).

Is there any reason to alias the lookup table as `codes`?

There are apparently lots of other clever uses of the WITH clause, such as https://www.postgresql.org/docs/current/queries-with.html#QU...

  CREATE TABLE vals (x, y);
  INSERT INTO vals VALUES (1, 1), (2, 0.5), (3, 0.4), (4, 0.1), (5, 0);
  
  WITH
     m(v) AS (
        SELECT ((COUNT(*) * (SUM(x * y))) - (SUM(x) * SUM(y))) / ((COUNT(*) * SUM(POW(x, 2))) - (POW(SUM(x), 2)))
        FROM vals
     ),
     b(v) AS (
        SELECT (SUM(y) - (m.v * SUM(x))) / COUNT(*)
        FROM vals JOIN m
     )
  SELECT
     x AS real_x,
     y AS real_y,
     m.v AS m,
     b.v AS b,
     x * m.v + b.v AS interp_y
  FROM vals JOIN m JOIN b;

  real_x  real_y  m      b     interp_y           
  ------  ------  -----  ----  -------------------
  1       1       -0.24  1.12  0.88               
  2       0.5     -0.24  1.12  0.64               
  3       0.4     -0.24  1.12  0.4                
  4       0.1     -0.24  1.12  0.16               
  5       0       -0.24  1.12  -0.0800000000000001