LATERAL is Your Friend to Create Local Column Variables in SQL

SELECT actor_id, name, name_length, COUNT(*) FROM actor JOIN film_actor AS fa USING (actor_id), LATERAL (SELECT first_name || ' ' || last_name AS name) AS t1, LATERAL (SELECT length(name) AS name_length) AS t2 GROUP BY actor_id, name, name_length ORDER BY COUNT(*) DESC LIMIT 5; SELECT actor_id, name, COUNT(*) FROM actor CROSS JOIN LATERAL (SELECT first_name || ' ' || last_name AS name) AS t JOIN film_actor AS fa USING (actor_id) GROUP BY actor_id, name ORDER BY COUNT(*) DESC LIMIT 5; SELECT actor_id, name, name_length, COUNT(*) FROM actor CROSS JOIN LATERAL (SELECT first_name || ' ' || last_name AS name) AS t1 CROSS JOIN LATERAL (SELECT length(name) AS name_length) AS t2 JOIN film_actor AS fa USING (actor_id) GROUP BY actor_id, name, name_length ORDER BY COUNT(*) DESC LIMIT 5;