Can We Read the Query in the Order We Explain the Job?

Botie needs pumps whose completed maintenance has consumed more than fifty minutes. Open jobs contain estimates that should not enter this report. A long nested query can make it difficult to see whether those estimates were excluded before the total was calculated.

We can give the qualifying jobs a name, then give the per-pump totals another name. Each stage describes a relation we can inspect.

Filter closed jobs, group their minutes by asset, then select totals exceeding the budget.

[
  {
    "asset_id": "P7",
    "total_minutes": 55
  }
]

WITH closed_jobs AS (...) places a name before the main query. Inside the parentheses is a SELECT describing the relation. Later stages and the final SELECT can refer to that name during this statement.

Start by selecting from closed_jobs directly. If open estimates remain, repair that boundary before adding aggregation.

A CTE is scoped to its statement. The next separate statement cannot assume that name still exists.

CREATE TABLE jobs(job_id INTEGER PRIMARY KEY, asset_id TEXT NOT NULL, status TEXT NOT NULL, minutes INTEGER);
INSERT INTO jobs VALUES (1,'P7','closed',20),(2,'P7','closed',35),(3,'P7','open',200),(4,'P8','closed',40),(5,'P9','closed',NULL);

WITH closed_jobs AS (
  SELECT job_id, asset_id, minutes FROM jobs WHERE status='closed'
)
SELECT job_id, asset_id, minutes FROM closed_jobs ORDER BY job_id;

Separate CTE definitions with commas. The totals stage can read closed_jobs and group by asset_id. Its output is now one row per asset with at least one qualifying job.

Give the aggregate a useful column alias such as total_minutes. That name lets the final filter describe the question without repeating the aggregate expression.

The same statement can contain several named stages without creating permanent objects.

CREATE TABLE jobs(job_id INTEGER PRIMARY KEY, asset_id TEXT NOT NULL, status TEXT NOT NULL, minutes INTEGER);
INSERT INTO jobs VALUES (1,'P7','closed',20),(2,'P7','closed',35),(3,'P7','open',200),(4,'P8','closed',40),(5,'P9','closed',NULL);

WITH closed_jobs AS (
  SELECT asset_id, minutes FROM jobs WHERE status='closed'
), totals AS (
  SELECT asset_id, SUM(minutes) AS total_minutes
  FROM closed_jobs GROUP BY asset_id
)
SELECT asset_id, total_minutes FROM totals
WHERE total_minutes > 50
ORDER BY asset_id;

At closed_jobs, job_id would still identify a row if we retained it. At totals, several job IDs have contributed to one asset row. Joining totals to another job-level table could multiply that summary again.

Write down the grain beside each stage when reviewing a query. A name such as data2 does little to help another person notice that the row meaning changed.

CTEs make a grain change visible; they do not prevent an incorrect later join.

The status condition describes individual jobs, so it belongs before their durations are added. The total threshold describes grouped assets, so it follows aggregation.

A direct GROUP BY with HAVING could express this same report. The named version is useful when we want to inspect or extend the intermediate relations. It is not automatically more correct or faster.

Compare the results of alternative forms on the same changed fixtures before deciding whether a rewrite preserves meaning.

An ordinary CTE is a query expression. Depending on the engine and the query, it may be folded into surrounding work or materialised as an intermediate result. Referencing its name twice is not a portable promise that expensive work runs exactly once.

SQLite documents MATERIALIZED and NOT MATERIALIZED hints, but these affect planning rather than changing the logical result. Begin without hints. Use the query plan and measurements before trying to force a strategy.

When a result must persist across statements, that is a different design involving a table or view.

Insert a large open job and predict that the completed report stays unchanged. Close that same job and predict that its asset can now pass the threshold.

The workshop keeps filtering, grouping and the final threshold on separate components. Inspect the output of the first component when the wrong jobs enter the total rather than trying to repair the final number.

The useful structure follows the question’s dependencies. Additional CTEs are helpful when they name a real intermediate meaning.