SQL subqueries versus CTEs: make an audit-friendly query

A subquery is a query used within another SQL statement. A common table expression, or CTE, gives a query result a name within a statement's WITH clause. Both can express intermediate steps. Choose the form that makes the logic and row grain easiest to review; neither spelling is a universal performance guarantee.

An analytical query becomes easier to audit when its stages correspond to business concepts: eligible orders, refund totals, adjusted values and final aggregation. Naming those concepts makes it easier to test assumptions and locate a mismatch.

We will calculate refund-adjusted completed-order value with the synthetic commerce dataset. The known reference is 104,000 paise of completed-order value minus 17,500 of refunds, leaving 86,500.

Begin with a derived-table subquery

SELECT SUM(o.order_total_paise - COALESCE(r.refund_paise, 0))
       AS adjusted_value_paise
FROM orders AS o
LEFT JOIN (
    SELECT order_id, SUM(refund_paise) AS refund_paise
    FROM refunds
    GROUP BY order_id
) AS r ON r.order_id = o.order_id
WHERE o.status = 'completed';

The subquery aggregates multiple refund events to one row per order before joining. That prevents refund events from multiplying the order header. It returns 86,500 paise.

This is a reasonable expression for a small calculation. The nested structure is not inherently bad. The problem appears when several eligibility rules, aggregations and enrichments become buried inside each other and their assumptions are hard to inspect.

Give the stages explicit names

WITH eligible_orders AS (
    SELECT order_id, order_total_paise
    FROM orders
    WHERE status = 'completed'
), refund_totals AS (
    SELECT order_id, SUM(refund_paise) AS refund_paise
    FROM refunds
    GROUP BY order_id
), adjusted_orders AS (
    SELECT
        o.order_id,
        o.order_total_paise,
        COALESCE(r.refund_paise, 0) AS refund_paise,
        o.order_total_paise - COALESCE(r.refund_paise, 0)
            AS adjusted_value_paise
    FROM eligible_orders AS o
    LEFT JOIN refund_totals AS r ON r.order_id = o.order_id
)
SELECT SUM(adjusted_value_paise) AS adjusted_value_paise
FROM adjusted_orders;

The answer is unchanged. The benefit is that each intermediate relation now has a business meaning and a stated grain:

The table is also a review checklist. If a later change introduces multiple rows per order in adjusted_orders, the reviewer knows which contract was broken.

Inspect intermediate results without rewriting the logic

During debugging, keep the WITH block and temporarily change the final SELECT to query the stage you need. Check counts, keys and totals before moving onward.

For example, inspect O1005 in adjusted_orders: its recorded value is 25,000 paise, refunds are 7,500 and adjusted value is 17,500. Looking at that one order makes it easier to spot a duplicate-refund join than staring at a grand total.

A CTE is scoped to its statement. You cannot run a separate later query against adjusted_orders as though it were a permanent table. For repeated investigations, a temporary table or saved view may be appropriate, with its own freshness and lifecycle considerations.

Readability does not establish execution strategy

Databases may inline, materialize or otherwise optimize CTEs and subqueries according to engine rules and query structure. Avoid teaching “CTEs are always faster” or “subqueries always execute once per row.” Those shortcuts confuse syntax with an execution plan.

Use EXPLAIN and representative data if performance matters. A tiny teaching fixture proves arithmetic and semantics, not the fastest plan for millions of records. The SQLite WITH documentation describes its CTE support; consult the equivalent reference for your production engine.

Also distinguish a derived table from a correlated subquery. A correlated subquery refers to values from an outer query, as in a NOT EXISTS absence check. It can be a clear way to express a different logical task; it is not merely an untidy version of a CTE.

Make names carry meaning

Names such as cte1, cte2 and final_final expose the editing history rather than the business logic. Prefer names that describe the population or transformation. Include eligibility in a name only when the stage actually enforces it.

Do not split every expression into a separate CTE. Excessive fragmentation forces a reader to jump around without gaining a clearer contract. A useful boundary is a change of grain, an eligibility rule or a transformation that deserves an independent check.

Exercise: extend the calculation to a specified reporting period. Decide whether the period applies to order creation, refund issuance or both. Name the stages so that another analyst can see the chosen policy. If the source has no refund timestamp, acknowledge that the second interpretation cannot be implemented from this fixture.

This style of reasoning supports the SQL and pipeline work in NeuraPath's Data Analytics with Generative AI course. A maintainable analytical query should reveal both how the result was calculated and which assumptions a reviewer needs to challenge.

Continue learning

This article is part of the SQL foundations for reliable analysis sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in UNION versus UNION ALL in monthly sales files.
• Continue with Calculate return rates with the right denominator.