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

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';

4. sources

https://mode.com/sql-tutorial/sql-case