Explaining (the need for) SQL's Common Table Expressions (CTE)

#sql

usecase

The concern is documenting the use of COMMON TABLE EXPRESSIONS as used repeatedly in Redash when creating monitoring queries / reports. Also, I ran into the order of SQL operations learning that you cannot filter a derived column in the same statement where it is defined.

1. what is CTE

A CTE (Common Table Expression) is TEMPORARY RESULT SET
You can reference within another SELECT, INSERT, UPDATE, or DELETE statement
i.e. you can build it and use in later query
used in more complex queries
they are EXPRESSIONS, i.e. they always return a value

2. syntax

3. benefits

easy to learn
code readibility
named query gives you info about query results
readable SQL is a big help to others and to you after 2 months
modularity
copy-pastable, re-usable
CTEs can be pasted together
better matches how you think about data analysis
Common Table Expressions (CTE) are often used to reduce the complexity of SQL queries.
Sometimes, CTEs are not just a refactoring technique; they are necessary if you want to filter on a derived column.

Sometimes, CTE is a necessity - not just a refactoring technique

See: https://mode.com/sql-tutorial/sql-case
The following breaks

select player_name,
       year,
       case when year ='SR' then 'yes'
       else null
       end as "is_a_senior"
from benn.college_football_players
where is_a_senior = "yes"

That returnes an error column "is_a_senior" does not exist occurs when referencing a derived column in the WHERE clause.
It is because the order of SQL query processing:
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT/OFFSET
The WHERE clause is evaluated before the SELECT clause, causing the derived column alias (is_a_senior) to be unavailable in the WHERE clause.
The simple solution using Common Table Expressions (CTE) (there may be others) is as follows:

WITH player_data AS (
    SELECT player_name,
           year,
           CASE WHEN year = 'SR' THEN 'yes'
                ELSE null
           END AS is_a_senior
    FROM benn.college_football_players
)
SELECT player_name,
       year,
       is_a_senior
FROM player_data
WHERE is_a_senior = 'yes';