5. Modularize Code with Common Table Expressions

CTEs create a temporary table that allow you to “query a query.”
https://www.essentialsql.com/introduction-common-table-expressions-ctes/

The advantage of a CTE (your second statement) over a derived table is that they can reference themselves and be used for recursion.

with avg_female_salary as (
   SELECT       AVG(salary) as avg_salary
   FROM       salaries
   WHERE gender = "Female"
)
SELECT name,  salary
FROM People
WHERE  salary >= (SELECT avg_salary FROM avg_female_salary)

7. Simplify Code using Temporary Functions

https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions

CREATE TEMPORARY FUNCTION seniority(tenure INT64) AS (
   CASE WHEN tenure < 1 THEN "analyst"
        WHEN tenure BETWEEN 1 and 3 THEN "associate"
        WHEN tenure BETWEEN 3 and 5 THEN "senior"
        WHEN tenure > 5 THEN "vp"
        ELSE "n/a"
   END
);
SELECT name
       , seniority(tenure) as seniority
FROM employees