When is a CTE that several reports now duplicate worth promoting to a view?

## The decision You wrote a CTE — say `active_customer` — inside one reporting query. Two more reports now need the same notion of "active", so someone copy-pastes the `WITH` block. Now three files carry the same definition. The question an interviewer is really asking is: what property of the situation tells you it is time to stop copying and create a stored object? ## The axis that decides it: how far the definition must reach A CTE's name lives for exactly one statement. That is a feature when the subresult is a step in one query's pipeline, and a hard limit when the subresult is a *concept* the organisation reuses. A derived table is even more local — its alias reaches only the block it sits in. A view is the only one of the three that other statements can name. So the rule is mechanical: **one statement → CTE or derived table; many statements → view.** Everything else is judgment about whether "many statements really need the same thing" is true. ## Three conditions worth checking before you promote **Is it genuinely the same rule?** Three reports that each filter customers slightly differently do not share a definition; they share a shape. Promoting the union of their needs produces a view with extra columns and a filter nobody agrees with, and each caller then re-filters it anyway. **Is the definition stable?** A subresult that changes shape every time the report changes is a bad candidate: each change is now a schema migration plus a redeploy of every dependent query. A definition that has been the same for months is a good candidate. **Does the name mean something outside the query?** `active_customer`, `billable_order`, `current_price` are business concepts. `step3_joined` is a stage of one pipeline and belongs inside that pipeline. ## What you gain One definition, in one place, that every query reads. When the meaning of "active" changes, you change it once. Readers of the dependent queries see a name that states intent instead of forty lines of filter logic. And a new report starts from a vocabulary rather than from a blank page. ## What you give up **Freedom to change it.** As soon as other people's queries say `FROM active_customer`, the view is an interface. Adding a column is usually easy; removing one, renaming one, or tightening the filter means finding and coordinating every caller. That is a real cost that a CTE, living inside one file, does not have. **Locality.** A reader of the report can no longer see the definition in front of them; they have to go look it up in the schema. For a stable, well-named concept that is a win; for a fiddly one-off it is a loss. **Lifecycle overhead.** A view is created and dropped by DDL, so it belongs in your migration process, and it needs an owner. A CTE is just text in a query file, reviewed and deployed with that file. **Parameters.** A view is a fixed query and takes no arguments. Callers narrow it by adding their own predicates against it — `SELECT ... FROM active_customer WHERE region = 'EU'`. If you truly need a parameterised subresult, the SQL construct for that is not a view; engines that offer table-valued functions provide that shape, and support varies, so check yours. ## A middle path Promotion is not all-or-nothing. Two reports that share a definition can be a signal to promote; five near-identical copies with small differences are a signal to first agree on what the definition *is*. It is also legitimate to keep a small view narrow — one meaningful filter, few columns — and let each report build its own pipeline of CTEs on top. Small, sharply-defined views compose; wide catch-all views become something everyone depends on and nobody can change. ## Worked shape ```sql -- One-off: the definition stays inside the statement that needs it WITH active_customer AS ( SELECT customer_id, name FROM customer WHERE status = 'ACTIVE' ) SELECT ac.name, COUNT(so.order_id) AS orders FROM active_customer ac LEFT JOIN sales_order so ON so.customer_id = ac.customer_id GROUP BY ac.name; -- Shared: one definition in the catalog, callers filter it themselves CREATE VIEW active_customer AS SELECT customer_id, name FROM customer WHERE status = 'ACTIVE'; ``` ## How to answer this in an interview Lead with scope — one statement versus many — because that is the mechanical part. Then show judgment: same rule or merely similar shape, stability, naming, and the cost of creating a shared dependency. Candidates who answer only "views are for reuse" miss the half of the question that separates a senior answer: what promoting it takes away from you. ## Common mistakes Promoting on the first duplication, before the definition has settled; creating a wide view that unions everybody's requirements; assuming a view can take parameters; and forgetting that a view, once other teams read it, cannot be changed on your own schedule.