SQL CTEs: WITH and a monthly report step by step

A query starts growing. First you count orders, then compare delivery categories, and finally need the best month. You can keep nesting SELECT statements, but eventually it becomes difficult to see which calculation feeds which result. At that point I like giving the stages names. That is where a CTE becomes useful.

Common Table Expression sounds more complicated than the construction itself. After WITH, you define a named expression and refer to it within the same statement. I will show that through a monthly report, with complete sample data, results you can verify, and a month without orders that is easy to lose when the calendar comes from the wrong source.

The examples are synthetic and target SQLite. Run the blocks in sequence in one empty connection. The original material used MySQL and DATE_FORMAT; this version uses consistently formatted text dates and SQLite functions. Executing these examples is not evidence that the same syntax has been tested in MySQL or another database engine.

A CTE has a name and a one-statement scope

The basic form is WITH name AS (...) SELECT .... The parentheses contain the query defining a result that you can reference by name. The final SELECT uses that name as a source. Everything belongs to one statement ending with a semicolon, rather than two independent queries separated by a semicolon in the middle.

A CTE does not create a permanent table that later appears in your database’s object list. Its name is available within the current statement. Once that statement ends, another standalone SELECT cannot simply read the previously named expression. If you need intermediate results across statements, consider a different mechanism, such as a temporary table.

I also do not assume that every CTE is physically stored in memory and read exactly once. The SQLite WITH documentation describes ordinary CTEs as acting similarly to views scoped to one statement. The planner determines execution. Naming a stage organizes the logic; it does not declare where the result must be stored.

Orders, missing values and a month without sales

Each row below represents one order. ship_mode has two possible values in this exercise. amount contains arbitrary points. One order has no known amount, while another has an amount of zero. I will preserve that distinction in the report instead of automatically converting every empty output field to a zero.

-- example: fixture
CREATE TEMP TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    ordered_at TEXT,
    ship_mode TEXT,
    amount INTEGER
);
INSERT INTO orders VALUES
    (1, 10, '2026-01-03', 'First Class', 100),
    (2, 10, '2026-01-08', 'First Class',  50),
    (3, 20, '2026-01-11', 'Second Class', 80),
    (4, 20, '2026-02-02', 'Second Class',120),
    (5, 30, '2026-02-09', 'First Class',NULL),
    (6, 30, '2026-03-01', 'First Class',  0);

January has three orders, February has two and March has one. April is absent from the records, but the report should cover all four months. That is a separate reporting-period requirement. You cannot recover it from events alone, because the absence of an event does not create a row in the orders table.

Dates consistently use YYYY-MM-DD. Taking the first seven characters therefore produces year and month here. This is an explicit exercise assumption, not a general parser for arbitrary dates. For a real source I check the type, time zone and rule for assigning events to periods. An event near midnight can belong to a different day in another zone.

Start by preparing shared fields

I will first name the common preparation step. The prepared CTE derives the month and selects fields needed downstream. The final read displays its result so that I can assess the stage independently. There is still one row per order at this point. Grouping will change that level of detail in the next stage.

-- example: prepared
WITH prepared AS (
    SELECT
        order_id,
        customer_id,
        SUBSTR(ordered_at, 1, 7) AS order_month,
        ship_mode,
        amount
    FROM orders
)
SELECT order_id, order_month, ship_mode, amount
FROM prepared
ORDER BY order_id;

I expect six rows. The first three belong to 2026-01, the next two to 2026-02, and the last to 2026-03. The fifth order’s amount remains NULL. Reading an intermediate stage helps catch problems before several aggregations hide their origin underneath totals that may look entirely plausible to the report’s recipient.

The name prepared is sufficient for this small example. In a larger report I would prefer a name describing the actual transformation. I do not introduce a stage merely to shorten a section by a few lines. I want a boundary where I can explain the columns, expected row count and meaning of one observation.

A later stage can use an earlier one

Next I aggregate the prepared records by month and delivery category. monthly refers to prepared. Commas separate definitions in the list after WITH. There is no trailing comma or semicolon after the final definition: the final read follows immediately. That boundary is a common source of syntax errors during early attempts with CTEs.

-- example: monthly
WITH prepared AS (
    SELECT
        customer_id,
        SUBSTR(ordered_at, 1, 7) AS order_month,
        ship_mode,
        amount
    FROM orders
),
monthly AS (
    SELECT
        order_month,
        ship_mode,
        COUNT(*) AS order_count,
        COUNT(DISTINCT customer_id) AS customers,
        COUNT(amount) AS known_amounts,
        SUM(amount) AS total_amount
    FROM prepared
    GROUP BY order_month, ship_mode
)
SELECT *
FROM monthly
ORDER BY order_month, ship_mode;

After aggregation, one row represents a month and delivery category. A single customer placed January’s two First Class orders. Order count and customer count therefore differ even though they use the same input. I would not automatically add distinct-customer counts across groups, because one person may appear in several of those groups.

known_amounts tells us how many orders have a known value. February’s First Class group contains one order and zero known amounts, so SUM returns NULL. March’s zero is instead the sum of a known value. The SQLite aggregate documentation explains the different roles of counting rows, counting nonmissing values and summing values.

Build the report from an explicit calendar

The report must cover January, February, March and April. I therefore define months independently of the order records. In a real model this could come from a calendar table. Here, four literal values fit directly inside a CTE, keeping the reporting scope visible without requiring another file or database object.

-- example: report
WITH months(order_month) AS (
    VALUES ('2026-01'), ('2026-02'), ('2026-03'), ('2026-04')
),
monthly AS (
    SELECT
        SUBSTR(ordered_at, 1, 7) AS order_month,
        ship_mode,
        COUNT(*) AS order_count,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY SUBSTR(ordered_at, 1, 7), ship_mode
),
first_class AS (
    SELECT * FROM monthly WHERE ship_mode = 'First Class'
),
second_class AS (
    SELECT * FROM monthly WHERE ship_mode = 'Second Class'
)
SELECT
    m.order_month,
    COALESCE(fc.order_count, 0) AS first_orders,
    fc.total_amount AS first_amount,
    COALESCE(sc.order_count, 0) AS second_orders,
    sc.total_amount AS second_amount
FROM months AS m
LEFT JOIN first_class AS fc ON fc.order_month = m.order_month
LEFT JOIN second_class AS sc ON sc.order_month = m.order_month
ORDER BY m.order_month;

Left joins retain months without matches. COALESCE supplies a zero order count when a group is absent. I do not automatically replace amounts: an absent group and a group with unknown amounts need different interpretations. The order count helps distinguish them here. A mixture of known and missing amounts would also require the completeness information from the earlier stage.

Compare February with April. February has a First Class order whose value is unknown. April has no orders in either category. If the recipient wants zero displayed for an empty month, that rule can be expressed separately. It should not accidentally present the unknown amount of an existing order as a known zero.

Why deriving months from orders is insufficient

If months selected distinct months from orders, April would never enter the report. A later LEFT JOIN cannot preserve a row that was absent from its left input. This is an easy mistake to make: choosing the correct join type does not repair an incomplete starting population.

The same issue appears in customer, product and working-day reports. To include people without purchases, you need a list of people, not merely customer identifiers found in transactions. A CTE makes that list easier to name and inspect, but deciding where the population comes from remains the responsibility of the person designing the analysis.

Calendar boundaries are a decision too. The four months in this example are explicit and finite. I do not automatically extend them to the largest source date, because a single incorrect year could alter the reporting scope. In a real process I define the reporting period and inspect out-of-period events as a separate data-quality check.

What the largest monthly total means

The monthly aggregation can feed another calculation. Below I select the largest known total for each category. The final two CTEs aggregate without GROUP BY, so each returns one row. Combining them with CROSS JOIN therefore produces one row containing two independent values, rather than a growing collection of combinations.

-- example: maxima
WITH monthly AS (
    SELECT
        SUBSTR(ordered_at, 1, 7) AS order_month,
        ship_mode,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY SUBSTR(ordered_at, 1, 7), ship_mode
),
first_best AS (
    SELECT MAX(total_amount) AS best_amount
    FROM monthly
    WHERE ship_mode = 'First Class'
),
second_best AS (
    SELECT MAX(total_amount) AS best_amount
    FROM monthly
    WHERE ship_mode = 'Second Class'
)
SELECT
    fc.best_amount AS first_best_amount,
    sc.best_amount AS second_best_amount
FROM first_best AS fc
CROSS JOIN second_best AS sc;

The answer is 150 and 120. These are independent maxima and may come from different months. The query does not return the months themselves or resolve ties between equally high totals. If the question concerns the best month, preserve its identifier and decide whether to show all tied months or choose one according to an additional rule.

With no available values, MAX returns NULL but the ungrouped aggregate still produces one row. That is why this particular cross join does not multiply the report. Do not generalize the observation to arbitrary CTEs. Four rows on one side and five on the other produce twenty combinations. I always establish the expected size of both inputs.

CTEs, subqueries, views and temporary tables

Many ordinary CTEs can also be written as subqueries in FROM. The choice often concerns readability and organization. A short transformation may fit comfortably in a subquery. For several stages, I prefer names I can refer to while discussing the report. That does not make a CTE a compulsory decoration for every SELECT statement.

A view stores a query definition as a database object. A temporary table stores data according to the engine’s rules. A CTE has the shorter scope of one statement. These constructs address partly overlapping needs, but differ in lifecycle, reuse and execution. I start by asking how long the result needs to exist and who needs to use it.

If several reports share a business definition, copying a long CTE between files can create divergent versions. A shared model layer may then be appropriate. The ability to paste SQL is not a good reason to maintain five independent definitions of the same measure. Readability within one file needs to coexist with consistency across the wider reporting process.

Does WITH change the execution plan?

I evaluate readability and performance separately. An engine may expand an ordinary CTE, use an intermediate result or apply other transformations allowed by its dialect. The sequence of names does not tell me that the database physically executes every stage from top to bottom and stores each result for later use.

When a statement is slow, I inspect its plan and measure execution on representative data. The number of rows read, filter placement and join relationships can all matter. This article’s tiny sample is designed for correctness checks. It cannot establish that one SQL form runs faster across an entire application or reporting environment.

Recursive CTEs are a separate topic. They can describe structures such as hierarchies, but require attention to termination and cycles. Our report uses ordinary CTEs only. A stage referring to an earlier named stage does not make the query recursive. Dependencies between stages and recursion are different constructions with different reasoning requirements.

Verify the report one stage at a time

I first make the final read select from the initial CTE and compare that result with the source. Then I inspect the aggregation: how many groups exist, what one row means and whether totals match a manual calculation. Only after those checks do I add joins. That keeps the possible sources of a new error reasonably small.

At each boundary I record the expected level of detail. monthly has one row per month and category. Restricting it to First Class leaves at most one row per month. That property allows it to join to the calendar without multiplying months. If an intermediate stage unexpectedly retains order identifiers, its row count may change and the final report can duplicate periods.

I check empty input as well. Without orders, the calendar-based report should still display four months, zero order counts and missing amounts. The maxima query should return one row with two missing values. Those are different expected output shapes. Saying that a query “handles empty data” is too vague to verify either behavior.

A useful debugging technique is to change only the final read while keeping the necessary definitions. Read monthly directly, then restore the report. Do not insert a semicolon between the definitions and the temporary diagnostic read, because that ends the statement. If the editor executes selected text, make sure the selection includes the definitions required by the chosen read.

Diagnose unexpected multiplication

If totals suddenly grow, inspect the join key on each side. In this example, the key is the month after restricting the category. Two January rows on one side and three on the other can create six matches. Adding DISTINCT to the final result does not explain the multiplication. Return to the stage that was supposed to provide one row per month.

A correct total with an incorrect customer count is another useful clue. Customer 10 placed two January orders, so counting rows gives two while counting distinct identifiers gives one. Neither number is universally preferable. Check whether the column name and the reader’s question refer to purchases, people or a different unit of observation.

Finally, distinguish definition order from display order. Defining first_class before second_class does not arrange the months in the output. The final report therefore has its own ORDER BY. Calculation dependencies, the engine’s physical plan and the table layout shown to a reader are separate concerns. Keeping them separate makes debugging much less dependent on guesswork.

Exercise and historical materials

Add a March Second Class order worth 40 points. Predict the effect before running the report: that category’s March order count should become one and its amount should become 40. Its highest monthly total should remain 120. April should remain unchanged. This checks aggregation, the calendar and the independent maximum calculation together.

Next add a known 25-point order to February’s First Class category. Its sum becomes 25 even though one amount is still unknown. Revisit known_amounts: one known value among two orders reveals incomplete data. The total alone does not tell you whether the source was complete. You also need information about the observations contributing to it.

The original article has an accompanying Polish video, available in the Polish version. This English tutorial stands on its own and uses the complete SQLite dataset above. You do not need the historical source database to execute these examples or reproduce their results. Keeping the datasets separate makes the scope of each exercise clear.

Historical downloads remain available: Orders Superstore CSV and the original MySQL code on GitHub. Treat them as a separate exercise variant. For the starting syntax, return to SELECT; for organizing a larger query, see the SQL formatting guide.