Your question is Subquery vs CTE vs Temp Table. Take a moment with it on the right.
Talk me through your thinking if you like. When you're confident, submit your answer and I'll grade it like a real screen (7/10 or better passes).
In PostgreSQL data pipelines, the same transformation can often be written as a nested subquery, a CTE, or a temporary table. The choice affects readability, optimizer behavior, reuse, and operational cost.
Explain the performance differences between a subquery, a Common Table Expression (CTE), and a temporary table. You should compare how PostgreSQL plans and executes each, when one is likely to be faster or slower, and how that changes in a pipeline that stages telemetry or event data from systems like Anduril Lattice.
Answer at the level of a data analyst or analytics engineer: discuss optimizer behavior, materialization, indexing, reuse across multiple steps, memory and disk implications, and when you would choose each approach for a one-off transformation versus a multi-step batch pipeline. You do not need to cover every PostgreSQL edge case, but you should call out common misconceptions and practical decision rules.