All articles

KajoData / SQL

CTEs in SQL: WITH, analysis stages and the right denominator

I build a delivery-share report from six orders. You will see how named stages help, where a filter can change the denominator, and why CTEs do not guarantee faster execution.

Kajo RudzińskiPublished Updated 12 min read

Connected platforms organise a stream of cubes through successive stages of analysis

The query works, but nobody wants to change it. One SELECT sits inside another, column names repeat in several places, and a week later you cannot explain the percentage in the final column. This is when I reach for a common table expression, or CTE.

The WITH clause lets me name an auxiliary result and use it in the same statement. Its practical value is that I can present analytical decisions in readable stages. I still have to understand what I am counting. Clear names cannot rescue a share calculated against the wrong denominator.

I will demonstrate this using six synthetic orders. The example targets PostgreSQL 18 and ordinary SELECT-based CTEs. Syntax and expected results follow the documentation. I do not assume that every SQL engine makes identical optimisation decisions.

A CTE has a name and a statement-level scope

The basic form contains WITH, a name, AS, a query in parentheses and a statement consuming the result. This structure is described in the PostgreSQL documentation. Naming the logic avoids pasting a whole subquery at its point of use.

WITH delivery_counts AS (
    SELECT delivery, COUNT(*) AS orders_count
    FROM orders
    GROUP BY delivery
)
SELECT delivery, orders_count
FROM delivery_counts
ORDER BY delivery;

This version assumes an existing orders table. I will replace it with explicit data shortly. First, notice the shape: delivery_counts contains one row per delivery method and acts as a named source for the final SELECT.

A CTE is neither a persistent table nor a saved view. A separate statement cannot access its name merely because the previous statement ran moments earlier. A semicolon ends the statement. Placing it before the main SELECT would break up something intended to form one complete instruction.

To define several CTEs, I use one WITH and separate definitions with commas. In the ordinary nonrecursive arrangement, a later stage can refer to an earlier one. Names should describe the output: orders_per_region communicates more than cte2. The reader should not have to remember a numbering scheme to understand the flow.

Define the question before dividing the code

I want the share of orders using each delivery method within a region and across the whole dataset. These require different denominators. Two express deliveries among four regional orders represent one half. The same two among six total orders represent one third.

The input grain is one row per order. Each identifier appears once, and both region and delivery method are populated. These are synthetic records, so I deliberately leave out basket lines, cancellations and unknown regions. Each would require a separate decision in a real source before calculating percentages.

order_id region delivery
1 North Express
2 North Express
3 North Standard
4 North Standard
5 South Express
6 South Standard

North contains four orders, South contains two and the entire dataset contains six. Both delivery methods occur in each region. I write these expectations down because they make accidental row multiplication or an unintended restriction easier to spot later.

I also define the output grain before writing code. Each report row should represent a region and delivery-method combination. It will no longer represent an individual order. This change of detail matters more than the presence of WITH in the query.

Build three named summaries

I need a count for each region and delivery combination, a regional denominator and a global denominator. They have different grains. The regional summary has one row per region. The global summary has one row holding the count of all orders.

The aggregate documentation distinguishes COUNT(*), which counts rows, from COUNT(column), which counts non-NULL values. I choose the first because I am counting orders. With incomplete identifiers, the second could quietly reduce my denominator.

The following query includes the entire input and the report:

WITH orders(order_id, region, delivery) AS (
    VALUES
        (1, 'North', 'Express'),
        (2, 'North', 'Express'),
        (3, 'North', 'Standard'),
        (4, 'North', 'Standard'),
        (5, 'South', 'Express'),
        (6, 'South', 'Standard')
), global_count AS (
    SELECT COUNT(*) AS all_orders
    FROM orders
), region_counts AS (
    SELECT region, COUNT(*) AS region_orders
    FROM orders
    GROUP BY region
), delivery_counts AS (
    SELECT region, delivery, COUNT(*) AS delivery_orders
    FROM orders
    GROUP BY region, delivery
)
SELECT
    d.region,
    d.delivery,
    d.delivery_orders,
    r.region_orders,
    g.all_orders,
    ROUND(100.0 * d.delivery_orders / r.region_orders, 2) AS pct_region,
    ROUND(100.0 * d.delivery_orders / g.all_orders, 2) AS pct_all
FROM delivery_counts AS d
JOIN region_counts AS r ON d.region = r.region
CROSS JOIN global_count AS g
ORDER BY d.region, d.delivery;
region delivery delivery_orders region_orders all_orders pct_region pct_all
North Express 2 4 6 50.00 33.33
North Standard 2 4 6 50.00 33.33
South Express 1 2 6 50.00 16.67
South Standard 1 2 6 50.00 16.67

There are four output rows, corresponding to the four combinations present in the input. I retain the counts and denominators alongside the percentages. Readers can then see that identical regional shares of fifty percent come from different numbers of orders.

Why CROSS JOIN is appropriate here

CROSS JOIN pairs every row from one source with every row from the other. Here the global aggregate, without GROUP BY, contains one row. Each region and delivery combination therefore receives exactly one global denominator. Four rows crossed with one remain four rows.

This follows the table-expression rules, rather than any special protection provided by a CTE. If I accidentally added grouping to global_count, I might create two or more denominators. Every report row would then repeat, while the numbers could still look plausible.

The regional summary must also be unique by region. That is the field connecting it to the more detailed summary. If the source contains several versions of a regional definition, I first need a selection rule. Calling the result region_counts does not enforce the right key.

When reading a query, I pause at every JOIN and ask how many partners a row can have. If I cannot answer, a final percentage is premature. The discussion of row counts after joins provides another way to examine this problem.

Division and rounding affect presentation

I multiply by 100.0 so that the calculation retains a fractional part. In PostgreSQL, division between integral types truncates towards zero. Dividing integer two by integer six first would produce zero. Multiplying afterwards cannot recover the lost fraction.

I round at the end to two decimal places. The displayed global shares happen to sum to one hundred here. Other distributions may produce a displayed total such as 99.99 after rounding. I do not silently adjust a random row without explaining the presentation rule.

Units matter too. 50.00 in a percentage column means fifty percent. Without multiplication by one hundred, the ratio would be 0.5. Either representation works when the label and format agree. A reporting tool that applies percentage formatting to an already scaled percentage can multiply it again.

With no input rows, the grouped summaries are empty and this report returns nothing. It does not automatically list regions with zero orders. That would require a separate list of all regions and an explicit decision to preserve zero-count groups. A missing row and a displayed zero communicate different things.

Filter placement defines the denominator

Suppose the reader wants to display only North while still comparing it with the whole dataset. I can add WHERE d.region = 'North' to the final query before ORDER BY. The output contains two rows, while the global denominator remains six.

If I remove South from orders instead, all three summaries see only North. The global denominator becomes four and each delivery method now represents fifty percent of the total. That is a different report, even if I leave the column headings unchanged. Part of the population disappeared before I calculated the whole.

Order statuses require the same decision. Does the express-delivery share concern all orders or only dispatched orders? Do cancelled orders belong in the denominator? There is no answer that fits every company. I need an agreed metric definition and must apply it consistently at the right stage.

Missing values also deserve attention. Our input contains no unknown regions. If NULL appears, GROUP BY creates a group for it, but the ordinary equality condition in the shown JOIN will not match two NULL values. I would need to decide on a missing-data bucket or a different join condition. Naming the problem comes before changing the code.

A sorting station separates cubes into regional compartments alongside a reference collection

Named logic does not dictate the physical execution plan

I read the stages from source to report, but that is no guarantee of a fixed physical calculation sequence. PostgreSQL can fold an ordinary, nonrecursive SELECT without volatile functions into its parent query. When the result is referenced several times, its default decision can differ.

The CTE materialisation documentation describes MATERIALIZED and NOT MATERIALIZED. The former forces separate calculation; the latter can force folding where applicable. Neither is a universal performance improvement. One choice can avoid repeated calculation, while the other can allow filters to move closer to the source.

I therefore do not promise that every CTE first fills memory with its complete result. Nor do I claim that an unused ordinary SELECT in WITH must be evaluated. Data-modifying statements in WITH have separate rules, and this lesson does not use them. Extending a simple reporting explanation to every form of WITH would be misleading.

When execution time is a problem, I inspect the EXPLAIN plan. I distinguish estimates from measurements. EXPLAIN ANALYZE executes the query, so choosing the environment and scope matters. Merely changing a subquery into a CTE provides no basis for claiming a particular percentage speed improvement.

Inspect the reasoning one stage at a time

If an output surprises me, I retain the relevant WITH definitions and point the final SELECT at an intermediate stage. I can inspect regional counts and compare them with the input without trying to read every output column at once. Row count, key uniqueness and the numerator definition are separate questions.

For six orders, the logical checks are straightforward. Counts for the delivery combinations must total six. Regional denominators must be four and two. Every combination must receive exactly one regional denominator and one global denominator. If those conditions fail, formatting percentages can wait.

I treat stage names as small promises to the reader. A stage called “all orders” is misleading if it hides a filter for one region. I either make its scope clear in the name or reconsider the filter. Readability includes agreement between words and logic, beyond simply breaking code into short blocks.

I also avoid turning every trivial operation into another CTE. Ten stages, several of which merely pass columns through, may make a report harder to trace. I place boundaries where the grain, population or calculation changes. Each stage then has a specific responsibility that another analyst can explain.

Explain what the delivery report actually says

The regional share describes the delivery mix inside a region. The global share describes the weight of one region and delivery combination in the entire dataset. Two fifty-percent regional shares do not mean equally large regions. North contains twice as many orders as South in this example.

I do not automatically turn this distribution into a story about customer preferences. Delivery method may reflect availability, an offer, a deadline or store rules. The table describes observed orders. Explaining their causes requires knowledge of the process and additional evidence. SQL can calculate the share without knowing why a customer chose an option.

I state exclusions as well. If the input covers one period, “all orders” means all orders in that period. It does not mean the company’s entire history. If the source contains no row for an inactive region, its absence from the report does not establish a failed import. A clear scope statement prevents unnecessary investigations.

When updating a report, I revisit the input contract. Adding product lines to the source may make one order identifier appear several times. COUNT(*) would then count lines even though its column name still suggested orders. Maintaining a metric requires checking source changes even when the SQL continues to run without syntax errors.

A total share needs a common counting base

Another trap appears when someone tries to shorten the report by averaging regional shares. Our original data hides the problem because each region has the same delivery mix. Change order 3 to Express and the different region sizes become significant.

North now has three express deliveries among four orders, while South has one among two. Their regional shares are seventy-five and fifty percent. The ordinary average is 62.5 percent. Across the dataset, however, express deliveries account for four out of six orders, or about 66.67 percent. The ordinary average gave equal weight to regions containing different numbers of orders.

This is another reason to retain numerators and denominators. I can add order counts and calculate the combined share on a common base. A percentage alone does not retain the size of its group. Exporting only rounded shares makes subsequent aggregation harder to get right.

The same issue arises when moving from weekly to monthly reporting. If weeks contain different order counts, their average percentage answers a different question from the monthly share of orders. Before adding another CTE, I establish whether I am aggregating events, groups or already calculated metrics. Similar-looking columns do not make these operations interchangeable.

Exercise: change one assumption and predict the effect

Add a seventh synthetic order with region South and delivery Express. North still has four orders, South now has three and the whole dataset has seven. Express represents two thirds of South, or 66.67 percent after rounding. That combination represents two sevenths of the entire dataset, or 28.57 percent.

Next, decide where to place a filter displaying North while keeping the global denominator at seven. Compare that with filtering the shared input. Explaining the difference in your own words matters more than copying every parenthesis. You should be able to identify exactly where the population changed.

Finally, name three useful stages for your own report. They might select valid events, summarise by customer and group customers by order count. For each stage, state what one row represents and what should be true of the output. For another worked context, see a histogram using CTEs and CASE WHEN.

Keep both share definitions beside the output. Someone receiving only the export should be able to distinguish the regional mix from its contribution to the whole without opening a SQL editor.

Key takeaways

Does a CTE create a table in the database?

It does not create a persistent user table. Its name is available within the statement containing WITH, while the physical calculation depends on the execution plan.

Does a CTE always make a query faster?

No. PostgreSQL may fold an ordinary CTE into its parent query or evaluate it separately. The effect depends on the data, references and plan.

Where should a region filter go when calculating a share of all orders?

If the denominator must include all regions, filter the displayed region after calculating that denominator. Restricting the shared input earlier changes what the share means.

AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.

Kajo Rudziński

Newsletter KajoData

Let’s keep in touch.

I write about data analysis, learning and changing careers. My newsletter and the sign-up form are in Polish. If that works for you, I’d be glad to see you there.