Rank-Then-Filter: "Customers Whose Latest Order Was Cancelled"

• Rank-then-filter – compute ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC, order_id DESC) to identify each customer's latest order, then in the outer query filter WHERE rn = 1 AND status = 'CANCELLED'.
• Why filter-first is wrong – applying WHERE status = 'CANCELLED' before ranking removes all non-cancelled orders, so "latest" is computed from only the cancelled ones, not from all orders.
• Different question answered – filter-first answers "customers who have any cancelled order" rather than "customers whose overall latest order was cancelled."
• General pattern – when a requirement is "the X-most row, and does it satisfy a condition," identify the X-most row on the full set first, then test the condition.
• Tie-breaker – ORDER BY created_at DESC, order_id DESC ensures the "latest" is deterministic.

What is the rank-then-filter pattern?

The question has two parts that must happen in a specific order: identify the latest order per customer, and test whether that order is cancelled. The correct query ranks inside a subquery or CTE, and then in the outer query filters on both the rank and the status.

-- Correct: rank first, then test status
WITH ranked AS (
    SELECT o.*,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY created_at DESC, order_id DESC
           ) AS rn
    FROM orders o
)
SELECT customer_id, order_id, status, created_at
FROM ranked
WHERE rn = 1 AND status = 'CANCELLED';

The rn = 1 pins down each customer's genuine latest order regardless of its status. Only after that row is fixed do we test whether its status is cancelled.

Why is filtering status before ranking wrong?

Filtering status first changes the population from which "latest" is computed. If a customer's actual latest order was completed and an earlier order was cancelled, the pre-filter deletes the completed latest order. The latest of the remaining cancelled orders looks like the latest order, but it is not.

-- Wrong: filter first, then rank
WITH cancelled_only AS (
    SELECT * FROM orders WHERE status = 'CANCELLED'
),
ranked AS (
    SELECT c.*,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY created_at DESC, order_id DESC
           ) AS rn
    FROM cancelled_only c
)
SELECT customer_id, order_id, status, created_at
FROM ranked
WHERE rn = 1;
-- This returns customers whose latest CANCELLED order exists,
-- not customers whose OVERALL latest order was cancelled.

What does each approach actually answer?

Consider this data for customer C001:

order_id | customer_id | status     | created_at
---------+-------------+------------+------------
O003     | C001        | COMPLETED  | 2024-03-15   ← actual latest
O002     | C001        | CANCELLED  | 2024-02-10
O001     | C001        | COMPLETED  | 2024-01-05

| Approach | Result for C001 | Why |
|----------|-----------------|-----|
| Rank-then-filter (correct) | Not returned | Latest order O003 is COMPLETED |
| Filter-then-rank (wrong) | Returned with O002 | After filtering, O002 is the only order left, so it looks "latest" |

The wrong approach falsely flags C001 because it threw away the truly latest order before ranking.

Why is this the general pattern?

The general rule is: when a requirement is "the X-most row, and does it satisfy a condition," you must first identify the X-most row across the full unfiltered set, then evaluate the condition on that identified row. Applying the condition as an early filter changes the population over which "X-most" is computed.

• The same pattern appears in:
• "Did the customer's first-ever login come from a blocked country?" Rank all logins, pick #1, then test the country.
• "Is the most expensive item in each order a restricted product?" Rank items by price, pick #1, then test the restriction.
• "Was the account's last transaction a reversal?" Rank all transactions by date, pick #1, then test the type.

-- Pattern: identify row, then test condition
WITH identified AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY group_key
               ORDER BY sort_key DESC
           ) AS rn
    FROM full_table  -- no pre-filtering!
)
SELECT *
FROM identified
WHERE rn = 1 AND condition = 'value';

What about using a correlated subquery instead?

You can express this with a correlated subquery that picks the max timestamp, but the window-function approach is cleaner and handles ties explicitly.

-- Correlated subquery approach
SELECT o.customer_id, o.order_id, o.status
FROM orders o
WHERE o.created_at = (
    SELECT MAX(o2.created_at)
    FROM orders o2
    WHERE o2.customer_id = o.customer_id
)
AND o.status = 'CANCELLED';
-- Problem: if two orders share the same max created_at, both are returned.
-- ROW_NUMBER with a tie-breaker avoids this.

What about deterministic tie-breaking?

A deterministic tie-breaker in the ORDER BY keeps the "latest" unambiguous. Without it, two orders with the same timestamp could swap positions between runs.

-- Non-deterministic (bad)
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC)

-- Deterministic (good)
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC, order_id DESC)

The order_id DESC ensures that among orders at the same timestamp, the one with the highest ID (most recently inserted) is chosen.

How do you implement this in Java?

package com.bank.service;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ChurnRiskService {

    private final Connection connection;

    public ChurnRiskService(Connection connection) {
        this.connection = connection;
    }

    public List<String> getCustomersWithLatestOrderCancelled() throws SQLException {
        String sql = """
            WITH ranked AS (
                SELECT customer_id, order_id, status, created_at,
                       ROW_NUMBER() OVER (
                           PARTITION BY customer_id
                           ORDER BY created_at DESC, order_id DESC
                       ) AS rn
                FROM orders
            )
            SELECT customer_id, order_id, created_at
            FROM ranked
            WHERE rn = 1 AND status = 'CANCELLED'
            """;
        List<String> atRisk = new ArrayList<>();
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                atRisk.add(rs.getString("customer_id"));
            }
        }
        return atRisk;
    }
}

What are common mistakes?

The most common mistake is filtering status in the WHERE clause before ranking, because it reads naturally in English ("find cancelled orders, then pick the latest") but produces the wrong result.

-- Wrong (reads naturally but is incorrect)
SELECT * FROM orders
WHERE status = 'CANCELLED'
ORDER BY created_at DESC;
-- This finds the latest CANCELLED order, not the latest order that happens to be cancelled.

-- Correct
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY customer_id ORDER BY created_at DESC, order_id DESC
    ) AS rn FROM orders
)
SELECT * FROM ranked WHERE rn = 1 AND status = 'CANCELLED';

What are common interview follow-up questions?

• "How would you find customers whose first order was cancelled?" Same pattern, change ORDER BY to created_at ASC, order_id ASC.
• "What if you need the second-most-recent order?" Filter WHERE rn = 2 instead of rn = 1.
• "Can you express this without a window function?" Yes, with a correlated subquery on MAX(created_at), but ties become ambiguous.