Build readable analysis with subqueries and CTEs

Estimated study time: 75–105 minutes. Read, predict, run, explain, then attempt the independent work.

A useful analysis often needs an answer from an earlier analysis. To find catalogue products above the average price, calculate the average first and compare each price against it. A scalar subquery returns one value, such as AVG(list_price). The outer WHERE can then use that value like another expression. If a scalar subquery returns multiple rows, PostgreSQL raises an error; it cannot guess which one you meant.

A different need is to test whether a relationship exists. EXISTS is true when its subquery finds at least one row. NOT EXISTS is true when it finds none. In the customer example, the subquery refers to c.customer_id from the outer query; this is a correlated subquery. Read it as “for this customer, is there any completed order?” The result is still one row per outer customer, rather than one row per matching order.

NOT EXISTS also avoids a subtle trap with NOT IN. If a NOT IN subquery contains a null, a nonmatching comparison can become unknown and be removed by WHERE. Use NOT EXISTS when the question is the absence of a matching relationship, and still be explicit about how null keys should be treated. Curated customer keys here are non-null.

Give intermediate results meaningful names

WITH introduces common table expressions, or CTEs. A CTE is a named query result available within the statement. It is not automatically a permanent table, and it is not a guarantee that the database materializes a physical copy. Use it first to make reasoning and testing clearer, not as a magic performance improvement.

The net-sales example has three stages. line_totals produces one row per order from item lines. refund_totals produces one row per order from refunds recorded before the reporting cutoff. order_net joins those summaries to completed orders inside the quarter and computes after-refund net sales. The final SELECT inspects individual order values. Each name says what the rows mean; each stage has an explicit key.

A left join is required for refunds because many completed orders have no refund record. Here COALESCE(refunded,0) has a justified meaning: within this complete synthetic refund ledger, no matching refund means no recorded refund amount. If a production ledger were incomplete, zero would be an assumption needing validation. line_totals uses an inner join because the fixture contains lines for every order; a real audit must check missing-line coverage separately.

Keep the reporting clock visible

The refund cutoff is before 1 April, and order inclusion uses the same quarter-end boundary. Refunds are assigned to the original order month. This reports the quarter's orders net of refunds known by the cutoff; it is different from a cash-flow report grouped by refund date. A late refund after 1 April would not belong in this particular as-of report.

We have current order status, not a status-history table. For this teaching fixture we assume that each stored completion was known on the order date. That assumption is deliberately stated because an actual historical report cannot infer past status from a current snapshot. Module 11 will make the same limitation important for AI features.

Build a CTE pipeline one stage at a time. Run the first stage's query alone, inspect keys and totals, then add the second. If the final result is wrong, inspect the earliest stage where an expected invariant fails. This approach is faster and more reliable than editing a long query randomly or asking a model to rewrite it without a diagnosis.

Worked example 08 scalar

The product-price average is a single scalar. The bag and guide exceed it. This uses catalogue prices because the question is about the current catalogue.

SELECT product_id,product_name,list_price FROM aabha_sql.products
WHERE list_price > (SELECT AVG(list_price) FROM aabha_sql.products)
ORDER BY list_price DESC,product_id;

Expected result from the bundled dataset:

The runnable file is sql/examples/08_scalar.sql.

Worked example 08 exists

The existence check does not multiply customers with several orders. It finds Gita and Hari because neither has a completed order.

SELECT c.customer_id,c.customer_name FROM aabha_sql.customers AS c
WHERE NOT EXISTS (SELECT 1 FROM aabha_sql.orders AS o
 WHERE o.customer_id=c.customer_id AND o.status='completed')
ORDER BY c.customer_id;

Expected result from the bundled dataset:

The runnable file is sql/examples/08_exists.sql.

Worked example 08 cte

Read from line_totals through order_net. Order 1005 has two refund records totalling 75, so 450 of line sales becomes 375 of net sales.

WITH line_totals AS (
 SELECT order_id,SUM(quantity*unit_price-line_discount) AS line_sales
 FROM aabha_sql.order_items GROUP BY order_id
), refund_totals AS (
 SELECT order_id,SUM(amount) AS refunded
 FROM aabha_sql.refunds WHERE refund_date < DATE '2025-04-01' GROUP BY order_id
), order_net AS (
 SELECT o.order_id,o.customer_id,o.order_date,o.channel,
 l.line_sales,COALESCE(r.refunded,0) AS refunded,
 l.line_sales-COALESCE(r.refunded,0) AS net_sales
 FROM aabha_sql.orders AS o
 JOIN line_totals AS l ON l.order_id=o.order_id
 LEFT JOIN refund_totals AS r ON r.order_id=o.order_id
 WHERE o.status='completed' AND o.order_date >= DATE '2025-01-01'
   AND o.order_date < DATE '2025-04-01'
)
SELECT order_id,line_sales,refunded,net_sales FROM order_net ORDER BY order_id;

Expected result from the bundled dataset:

The runnable file is sql/examples/08_cte.sql.

Independent practice

08a

Return customer_id and customer_name for customers who have at least one refund on any order. Return each customer once.

Hint: Use EXISTS with the orders-to-refunds relationship inside it.

08b

Starting from the order_net CTE pattern, return channel and total net_sales for the quarter, ordered by channel.

Hint: Keep the cutoff and refund rules unchanged; only the final aggregation changes.

Check before moving on

You can state the grain and key of each CTE, explain why refunds use a left join, and distinguish an as-of sales report from refund-date cash flow.