Lesson 6 of 7 · 55 min

Explain comparisons with CTEs and window functions

Learn the reasoning. Try the task. Compare your answer.

Prepared for DataCareerHub · Curriculum version 2026-10-15.3

By the end of this lesson

Use a CTE to separate aggregation from comparison.
Calculate prior-month changes without inventing a first-period value.
Build a running total with an explicit order and frame.

Use the practice filesReady-to-open SQLite database ↓14 practice starters ↓Worked SQL solutions ↓Setup & data dictionary ↓All datasets & instructions ↗

SQLite syntax · Read-only exercises run on your local practice copy · Monetary inputs are integer cents, converted to EUR in the query outputs.

Understand the method

A common table expression, written with WITH, names a query result for the rest of a statement. It can make analytical steps easier to read: first define a population, then aggregate, then compare. A CTE is not automatically a saved table, a new permission boundary or a performance guarantee. Use it when the named step clarifies the meaning of the calculation.

Separate monthly totals from comparisons

The monthly CTE groups completed orders into February and March sales in cents. A second CTE uses LAG(sales_cents) OVER (ORDER BY month) to place the previous observed row's sales beside the current row. The outer query converts cents to euros and computes percentage change.

LAG reads a previous row in the specified window order. It does not independently prove the previous row is the previous calendar month. If a month is missing from the result, the prior observed row might be two or more months earlier. To support a strict calendar-month comparison, first build and validate an appropriate calendar series and its coverage. This fixture has both consecutive covered months, so the comparison is defined.

Missing and zero bases are different

There is no prior observed month for February, so its previous value and growth remain SQL NULL. Do not replace that missing history with zero and call the resulting comparison valid. March compares €600 with €500: 100.0 * (60000 - 50000) / 50000 gives 20.0 percent.

NULLIF(previous_sales_cents, 0) returns NULL for a zero denominator. The calculation then remains NULL instead of dividing by zero. A zero prior total is an observed value with an undefined percentage change; a missing prior row is absent history. Both need a nonnumeric percentage result, but the explanation is different. ROUND to one decimal at the presentation layer while keeping cents totals exact.

Window aggregates preserve rows

A normal GROUP BY collapses order rows into monthly rows. A window SUM can then operate over those monthly rows while retaining one output row for each month. Q13 uses an explicit frame:

SUM(sales_cents) OVER (
  ORDER BY month
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

For February this is €500. For March it is €500 + €600 = €1,100. Do not SUM the running-total column to get overall sales; that would count February twice. The final running-total value equals the covered cumulative total.

Keep the dialect explicit

The practice uses SQLite's substr function and window syntax supported by modern SQLite. Window support began with SQLite 3.25.0. Run SELECT sqlite_version() in your editor if a window query is rejected. Other database systems use different date helpers and may have different division behaviour; do not paste the file into a different production engine and assume the same result.

CTEs and windows are introduced here as readable analytical tools, not shortcuts around validation. Check row counts, period order, source filters and units at each named step. A polished final column can still be wrong if its monthly input counted duplicated joined rows.

Your turn

Practical exercise

Complete Q12 and Q13. Q12 returns monthly sales, previous observed sales and growth percent. Q13 returns monthly and cumulative sales. Explain February's NULL comparison and why a missing month would change the interpretation of LAG. As a separate reasoning test, explain how the percentage should be presented if the prior observed total were exactly zero.

Deliverable: two window queries and a note that distinguishes missing history, zero denominators and calendar coverage. Do not replace the first-period NULL with a fabricated growth figure.

Open the worked solution

WITH monthly AS (
    SELECT substr(order_date, 1, 7) AS month, SUM(order_value_cents) AS sales_cents
    FROM orders WHERE status = 'completed'
    GROUP BY substr(order_date, 1, 7)
), compared AS (
    SELECT month, sales_cents,
           LAG(sales_cents) OVER (ORDER BY month) AS previous_sales_cents
    FROM monthly
)
SELECT month, sales_cents / 100.0 AS sales_eur,
       previous_sales_cents / 100.0 AS previous_sales_eur,
       ROUND(100.0 * (sales_cents - previous_sales_cents)
             / NULLIF(previous_sales_cents, 0), 1) AS growth_pct
FROM compared ORDER BY month;

February is €500 with previous sales NULL and growth NULL. March is €600, previous sales €500 and growth 20.0. NULLIF handles a zero prior total by leaving growth unavailable. The query compares previous observed rows; continuous monthly coverage must be checked separately.

WITH monthly AS (
    SELECT substr(order_date, 1, 7) AS month, SUM(order_value_cents) AS sales_cents
    FROM orders WHERE status = 'completed'
    GROUP BY substr(order_date, 1, 7)
)
SELECT month, sales_cents / 100.0 AS sales_eur,
       SUM(sales_cents) OVER (
           ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) / 100.0 AS running_sales_eur
FROM monthly ORDER BY month;

The running totals are €500 and €1,100. The final value is the complete two-month completed-sales total. Summing both running totals would give €1,600, an incorrect double count. The explicit ROWS frame documents that the cumulative calculation includes all earlier monthly rows through the current row.

Check your understanding

One more question.

SQL references: SELECT and joins, aggregate functions, window functions.

How these learning materials are prepared and maintained · Report a correction: support@datacareerhub.io

Read and practice without an account. Sign in with an existing account to save lesson completion.

Next lesson →

Fictional training data. Portfolio practice is not a claim of client work or an accredited qualification. Explore practical lessons, worked examples and projects. Follow a published path, practise the method and build work you can explain.