Advanced SQL queries: CTEs, windows and rankings in one example

For me, advanced SQL begins when I can explain what every stage of a calculation means. Adding WITH, several joins and a window function does not guarantee that understanding. An elegant query can calmly double revenue or hide a customer who has not bought anything.

I will therefore build one analysis using a shared dataset. I want to summarize paid January orders, compare customers, preserve ties and calculate a running order total. Along the way I use a subquery, CTEs, aggregation, a join and window functions. Each tool gets a specific job.

The examples use synthetic data and SQLite with window-function support. I execute them in an in-memory database through node:sqlite in Node 24. I do not mix them with SQL Server’s dynamic SQL syntax. When moving the ideas to another engine, I check data types, date handling, division and query-planning rules.

Define the result before writing the query

One row in the customer table represents one customer. One row in the order table represents one order. Amounts are integers in an arbitrary shared unit, which makes the example easy to check by hand. All records belong to one system and use that same unit.

The summary should contain one row per customer, including customers without purchases. The ranking should include customers with paid orders and complete amounts. Those are two different outputs. Having no purchases and having missing amount data require different interpretations, even when both can appear as zero on screen.

I write those requirements in ordinary sentences first. They help me decide whether a condition belongs before or after aggregation. A query returning a table is a very weak definition of success. It will usually return one even when I have completely misunderstood the business question.

A small dataset should include awkward cases

I deliberately include two customers with equal totals, a customer with both a missing amount and a valid zero, a customer without orders, a cancelled purchase and a purchase from the following month. Every important decision then affects something visible. Five randomly selected tidy rows could hide most of the mistakes.

Run the table-creation block once in a fresh practice session. The subsequent queries share these temporary tables. Do not run the setup in a production database. Here, everything fits in memory and disappears when the connection closes.

-- example: fixture
CREATE TEMP TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TEMP TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  ordered_at TEXT NOT NULL,
  status TEXT NOT NULL,
  amount INTEGER
);
INSERT INTO customers VALUES
  (1, 'Ada'), (2, 'Ben'), (3, 'Celina'), (4, 'Dan');
INSERT INTO orders VALUES
  (101, 1, '2026-01-01', 'paid', 100),
  (102, 1, '2026-01-01', 'paid', 50),
  (103, 2, '2026-01-02', 'paid', 150),
  (104, 2, '2026-01-02', 'cancelled', 999),
  (105, 3, '2026-01-03', 'paid', NULL),
  (106, 3, '2026-01-03', 'paid', 0),
  (107, 1, '2026-02-01', 'paid', 200);

Dates use one ISO format without times or time zones. Under these assumptions, text comparison preserves day order. In a real system, I establish the timestamp type and reporting time zone. An export mixing several date formats would no longer satisfy the assumptions of this exercise.

Use a time interval with an exclusive endpoint

I select records from the first of January inclusive to the first of February exclusive. This makes the month boundary explicit. If times appear later, I do not have to guess how many nines to append to the final second of the final day.

Five paid orders should remain in our dataset. Four have known amounts. Their known total is three hundred and their average known amount is seventy-five. The cancelled purchase and the February purchase are outside this population. I record these numbers as a reference for later checks.

I pay particular attention to matching the population in a subquery with the population in the outer query. Comparing January orders with an average that also includes February can be a legitimate analysis, but it must follow from the question. If the time range changes accidentally, I get a different metric without any syntax error.

A subquery supplies a value for comparison

I want to find known amounts greater than the average for the selected population. First I name that population in a CTE, then use a scalar subquery in the condition. The average produces a single value, which I can compare with each order amount.

-- example: above-average
WITH paid AS (
  SELECT * FROM orders
  WHERE status = 'paid'
    AND ordered_at >= '2026-01-01'
    AND ordered_at < '2026-02-01'
)
SELECT order_id, amount
FROM paid
WHERE amount > (SELECT AVG(amount) FROM paid)
ORDER BY order_id;

The result contains order 101 with an amount of one hundred and order 103 with an amount of one hundred and fifty. AVG ignores NULL but includes zero. A missing amount does not pass the comparison. The SQLite aggregate documentation describes these rules.

This query answers a question about individual orders. To find customers above the average customer value, I would first need to calculate customer values. Changing the level of analysis changes the average’s denominator. Using the same function does not automatically answer the same business question.

A CTE names a stage within one statement

The expression WITH paid AS (...) defines my selected population once so I can refer to it later in the statement. A CTE exists within that one statement. A subsequent independent query needs its own definition. I do not treat it as a permanently stored results table.

At this stage, its main benefit is readability. I can name successive steps paid, totals and ranked, instead of nesting several large blocks without an obvious purpose. A useful name says what has already been calculated and helps me verify the detail level of each intermediate result.

Writing a CTE does not prove that the engine executes the stage exactly once or makes the query faster. The WITH documentation also discusses materialization hints and the optimizer’s role. I first use CTEs to organize my reasoning. Performance claims come after inspecting a plan and measuring representative data.

Aggregate orders before joining the customer list

I summarize orders by customer and then join those totals to the complete customer list. Each side of the join has at most one row per customer. That makes the expected output row count straightforward to check.

-- example: summary
WITH totals AS (
  SELECT customer_id, COUNT(*) AS order_count,
         COUNT(amount) AS known_count, SUM(amount) AS known_total
  FROM orders
  WHERE status = 'paid'
    AND ordered_at >= '2026-01-01'
    AND ordered_at < '2026-02-01'
  GROUP BY customer_id
)
SELECT c.customer_id,
       COALESCE(t.order_count, 0) AS order_count,
       COALESCE(t.known_count, 0) AS known_count,
       COALESCE(t.known_total, 0) AS known_total
FROM customers AS c
LEFT JOIN totals AS t ON t.customer_id = c.customer_id
ORDER BY c.customer_id;

Ada has two orders, two known amounts and a total of one hundred and fifty. Ben has one order, one known amount and the same total. Celina has two orders, one known amount and zero. Dan has zeros throughout. Celina’s result represents the sum of known amounts, rather than a confirmed complete order value.

The expression COUNT(*) counts records in a group, while COUNT(amount) counts records with a non-null amount. Their difference reveals missing data. Here, COALESCE helps display the result but cannot replace that check. When all amounts are unknown, showing zero without a missing-value count would be misleading.

A join can change what a total means

Imagine an additional customer-tag table. Ada has two tags and Ben has one. If I join orders to tags before aggregation, every Ada order appears twice. The amount looks reasonable in each row while the total stops describing sales.

I begin by asking why I need the tags. If I only need to check whether a particular tag exists, I can use an existence condition. If I need a report by tag, I must decide how to allocate value across multiple categories. The sum across categories may then legitimately differ from the global total.

I do not automatically fix this by adding DISTINCT to the amount. Two different orders can have equal amounts and both still need to count. My SQL joins article shows row multiplication in detail. Here, the safeguard is deliberately aggregating to the appropriate level before joining.

A ranking needs rules for ties and missing values

I admit customers with paid orders and complete amounts into the ranking. HAVING removes groups containing missing amounts. A window function then assigns ranks by total, and the final stage selects first place. Each stage has a separate, explicit responsibility.

-- example: ranking
WITH totals AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  WHERE status = 'paid'
    AND ordered_at >= '2026-01-01'
    AND ordered_at < '2026-02-01'
  GROUP BY customer_id
  HAVING COUNT(amount) = COUNT(*)
), ranked AS (
  SELECT customer_id, total,
         RANK() OVER (ORDER BY total DESC) AS position
  FROM totals
)
SELECT customer_id, total, position
FROM ranked
WHERE position = 1
ORDER BY customer_id;

Ada and Ben both receive rank one with a total of one hundred and fifty. Celina is excluded because her amounts are incomplete. Dan has no orders and never enters the aggregation. Exclusion should be explained beside the report so readers do not confuse it with a zero score.

The RANK function leaves gaps after ties, whereas DENSE_RANK does not. Both produce the same leaders when selecting first place alone. ROW_NUMBER assigns consecutive positions, so choosing position one retains a single record. If I genuinely need one winner, I define another rule, such as the earlier date on which the qualifying result was reached.

Adding a unique identifier to the ranking order would break the tie, rather than simply making an equal rank prettier. I keep the business measure inside the ranking order and use a separate final sort to display tied customers predictably. That small distinction is easy to lose during a formatting cleanup.

Filtering before a window changes its population

In SQLite, I do not filter a window-function result using WHERE at the same query level. I calculate the window in a CTE or subquery, then filter in an outer query. This separation also clarifies what the analysis means.

If I restrict customers to one region before assigning ranks, I obtain a ranking within that region. If I calculate global ranks first and filter by region afterwards, I see the global positions of customers from that region. Both outputs can be useful, but they answer different questions.

Date filters affect running totals in the same way. Removing earlier months before the window means the running total starts at the beginning of the visible period. A balance that includes historical activity needs earlier rows or a separately calculated opening balance. Filter order belongs to the metric’s definition, rather than merely the query’s appearance.

A running total needs an unambiguous order

I now preserve individual orders with known amounts and add a total from the start of the selected period to each row. I order by date and order identifier. The second column settles the order of the two purchases made on the same day.

-- example: cumulative
SELECT order_id, amount,
       SUM(amount) OVER (
         ORDER BY ordered_at, order_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders
WHERE status = 'paid' AND amount IS NOT NULL
  AND ordered_at >= '2026-01-01'
  AND ordered_at < '2026-02-01'
ORDER BY ordered_at, order_id;

The successive totals are one hundred, one hundred and fifty, three hundred and three hundred. The last order has a zero amount, so it does not increase the sum but remains a separate row. The order with an unknown amount is explicitly excluded. I describe the result as a running total of known amounts.

Adding PARTITION BY customer_id would restart the calculation for each customer. Here, I deliberately calculate one shared series. The order inside the window controls the calculation, while the final ORDER BY controls display order. I do not assume that one automatically supplies the other.

The default frame can include the entire tie

For an aggregate window with ORDER BY, SQLite’s default frame includes rows that are peers under that ordering. If I order only by date, the two orders on the first of January are peers. Both show a total of one hundred and fifty, even if I intuitively expect one step for each order.

-- example: frame-peers
SELECT order_id,
       SUM(amount) OVER (ORDER BY ordered_at) AS day_total
FROM orders
WHERE status = 'paid' AND amount IS NOT NULL
  AND ordered_at >= '2026-01-01'
  AND ordered_at < '2026-02-01'
ORDER BY ordered_at, order_id;

The outputs are one hundred and fifty, one hundred and fifty, three hundred and three hundred. The earlier explicit ROWS frame with an unambiguous order produces a record-by-record running total. The date-based version can help show the total at the end of each day, while still preserving individual orders.

The window-function documentation explains frame differences. I first decide whether the calculation should advance by record, by day or by a group of equal values. Then I choose the syntax. Once the data contains ties, a default setting quickly becomes a substantive analytical decision.

EXISTS answers a question about presence

When I need customers with at least one paid January order, I do not need every customer-order pair. The EXISTS condition expresses that question directly, retaining one outer-table row per customer.

-- example: exists
SELECT c.customer_id
FROM customers AS c
WHERE EXISTS (
  SELECT 1 FROM orders AS o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'paid'
    AND o.ordered_at >= '2026-01-01'
    AND o.ordered_at < '2026-02-01'
)
ORDER BY c.customer_id;

The result contains customers one, two and three. Celina qualifies even though one amount is missing. I am asking whether a paid order exists, so amount completeness is outside this condition. If the question changes, I change the condition explicitly.

I do not promise that this form always outperforms every correctly written join. Its advantage here is that the query closely matches the intention. An explicit intention makes review easier; the execution plan shows how the engine implements it for the actual dataset.

Investigate performance after correctness

Almost everything is fast with seven orders. This dataset is for checking logic, rather than benchmarking performance. A useful measurement needs representative volume, distribution, indexes and execution conditions. I also record whether the query benefited from an already warm cache.

The EXPLAIN QUERY PLAN documentation describes how to inspect table access and index use. A plan is not a stopwatch. Seeing an index in the plan does not guarantee a faster result, and scanning a small table may be a sensible optimizer decision.

The previous version of this article included dynamic SQL specific to SQL Server. I do not transplant it into this SQLite exercise. Variable values belong in query parameters. Column names and sorting directions need a separate controlled selection from permitted options. A value parameter cannot replace arbitrary pieces of SQL syntax.

Before changing a slow query, I save its expected output. Otherwise, an apparently successful optimization may simply calculate less work by dropping important records. Comparing row counts, identifiers and totals gives me a baseline for deciding whether the faster version still answers the original question.

Test the queries by changing the data

I also place a short population description beside the output table: the period covered, excluded statuses and the number of records missing an amount. If that explanation exists only in a SQL comment, someone reading a spreadsheet export may never see it. A technically correct result can then receive a much broader interpretation than it supports.

When handing over a query, I ask another person to calculate one customer manually. Two orders and one missing value are enough to check whether we understand the definition in the same way. I compare whole-dataset totals afterwards. Checking a small part is especially useful when two mistakes happen to cancel each other out in the final number.

Add a customer without orders. The summary should gain a row with zero counts, while the ranking remains unchanged. Next, replace Celina’s missing amount with one hundred and fifty. Her complete total now equals Ada’s and Ben’s, so the ranking should show three leaders.

Restore the original data and delete every order. The summary should still show four customers. The ranking, above-average selection and running total should be empty. Then remove the customers too. An empty result becomes the correct answer, rather than a failure to conceal with a fabricated record.

Also test a record dated exactly the first of February. It must stay outside January. These small changes exercise boundaries that are easy to overlook in a large report. For another task with the same discipline, build a SQL order histogram. The number of techniques in a query matters only after I can defend its result.