CTE Mistakes I See Every Day

Welcome to **Day 55**. Today we're doing some "Code Review." I see these four CTE mistakes constantly when looking at junior-to-mid level code.

Mistake 1: The "Everything CTE"

Don't put thousands of columns into a CTE just because you "might" need them. Data analysts call this "Widening" the query. Only SELECT the columns necessary for the next step.

Mistake 2: Bad Naming

Avoid names like `cte1`, `cte2`, or `temp_res`. If I have to scroll up to remember what your CTE does, you've failed at readability. Use `filtered_orders` or `revenue_by_region`.

Mistake 3: Forgetting about Materialization

In older versions of PostgreSQL, CTEs were always "Materialized" (Calculated fully before any filtering). This was slow!

Modern databases are smarter, but if your CTE is very slow, you might need to reconsider if it's being used efficiently.

Mistake 4: Not using CTEs for Deleting

Did you know you can use CTEs with `DELETE`?

WITH expired_users AS (

SELECT id FROM users WHERE last_login < '2020-01-01'

)

DELETE FROM users WHERE id IN (SELECT id FROM expired_users);

Many people forget this and write messy, dangerous subqueries instead.

Your Task for Today

Check your current CTE names. Are they descriptive enough?

*Day 56: Common Table Expressions for Data Cleaning.*