Computed Columns and Expressions

Derive, don't store

A row in order_items gives you qty and unit_price_cents, but there is no line-revenue column. You could ask the source system to store one, but then every write has to keep it in sync with its inputs forever, and the day someone updates qty without recomputing revenue, your table lies. The data-engineering default is to derive the value at query time: qty * unit_price_cents AS line_revenue_cents. The formula lives in one place, your model, and it can never drift from the columns it depends on.

The same logic applies to labels. Instead of storing a full_name, you build it on read with first_name || ' ' || last_name. One source of truth, computed when queried.

An expression is a per-row computation

Each item in the SELECT list is an expression the engine evaluates once per row. A bare column like product_id is the simplest expression; qty * unit_price_cents is a richer one. The AS alias names the resulting output column. Nothing is written back to the table, so you are shaping the result set only.

Three building blocks cover most projections:

• Arithmetic: +, -, *, / operate on numeric columns and literals, row by row.
• Concatenation: || glues text together. You can splice in literal strings, so sku || ' (' || category_code || ')' yields values like SKU-AUDIO-01 (AUD).
• Literal columns: a constant becomes the same value on every row: 'ecommerce_raw' AS source_system.

In the warehouse this differs. Postgres and Snowflake also use || for concatenation, but SQL Server uses + and BigQuery uses CONCAT(). || is the portable ANSI choice and what SQLite understands, so we author with it here.

Worked example

SELECT
  product_id,
  qty * unit_price_cents      AS line_revenue_cents,
  sku || '-' || category_code AS product_label,
  'ecommerce_raw'             AS source_system
FROM order_items;

product_id  line_revenue_cents  product_label     source_system
----------  ------------------  ----------------  -------------
501         3000                SKU-AUDIO-01-AUD  ecommerce_raw
502         4999                SKU-AUDIO-02-AUD  ecommerce_raw
501         4500                SKU-AUDIO-01-AUD  ecommerce_raw

Every column is expression AS output_name: qty * unit_price_cents is the expression, line_revenue_cents is the name it lands under.

Pitfalls

Integer division truncates. When both operands are integers, SQLite does integer math, so unit_price_cents / 100 on 4999 gives 49, not 49.99. Force a decimal by dividing by 100.0 (or multiplying one side by 1.0). The result type is inferred from the operands, so one decimal operand is enough.

NULL poisons the whole expression. Any arithmetic or concatenation that touches a NULL returns NULL, so if last_name is NULL, then first_name || ' ' || last_name is NULL, not just the first name. Guard with COALESCE(last_name, '') when a missing part should not blank out the row.

Always alias a computed column. An un-aliased expression gets an unstable auto-name like qty * unit_price_cents that downstream code cannot rely on.

Interview nuance: aliases are minted in the SELECT step, which the engine logically evaluates after WHERE and GROUP BY but before ORDER BY. So in standard SQL (Postgres included) you cannot filter on line_revenue_cents by its alias in the same query's WHERE; you repeat the expression or wrap it in a subquery. ORDER BY line_revenue_cents works because ordering happens last. SQLite is lenient and will accept the alias in WHERE, but do not lean on that when portability matters.

Order of evaluation

1. FROM / JOINassemble the rows
2. WHEREkeep rows that pass
3. GROUP BYcollapse into groups
4. HAVINGkeep groups that pass
5. SELECTproject columns + window fns
6. ORDER BYsort the result