Customers With BOTH State A and State B: Conditional Aggregation

• GROUP BY customer_id – makes one group per customer so all their orders are in one bucket.
• COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END) – counts only completed orders within each customer's group; non-matching rows produce NULL, which COUNT ignores.
• HAVING ... > 0 AND ... > 0 – requires both conditional counts to be above zero, ensuring the customer has at least one order of each status.
• Single pass – the table is scanned once, unlike the self-join or INTERSECT approaches that scan it multiple times.
• FILTER clause – PostgreSQL's COUNT(*) FILTER (WHERE ...) is a modern standard-SQL alternative that reads more clearly.

What does it mean to find customers with BOTH state A and state B?

This problem asks you to find entities (customers, users, accounts) that have at least one row of state A and at least one row of state B in the same table. At the National Bank of India, the fraud team flags customers who have both a completed and a cancelled order, because that combination can indicate card testing.

-- Sample data
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status      VARCHAR(20) NOT NULL,
    amount      DECIMAL(15,2) NOT NULL
);

INSERT INTO orders (customer_id, status, amount) VALUES
(1001, 'COMPLETED', 45000.00),
(1001, 'CANCELLED', 12000.00),
(1002, 'COMPLETED', 78000.00),
(1002, 'COMPLETED', 32000.00),
(1003, 'CANCELLED', 15000.00),
(1003, 'CANCELLED',  8000.00);

How do you write the query with conditional aggregation?

Group by customer and use conditional counts in the HAVING clause. Each CASE expression returns 1 for matching rows and NULL for non-matching rows. COUNT ignores NULLs, so each conditional count tallies only the rows of that status.

SELECT customer_id
FROM   orders
GROUP BY customer_id
HAVING COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END) > 0
   AND COUNT(CASE WHEN status = 'CANCELLED' THEN 1 END) > 0;

 customer_id
-------------
        1001

Only Rajesh (1001) has both statuses. Priya (1002) has only completed orders, and Pankaj (1003) has only cancelled orders.

Why does COUNT(CASE WHEN ...) work?

The CASE expression returns NULL when the condition is false (no ELSE clause). COUNT ignores NULL values, so it only counts the rows where the CASE returned 1. This is how you get a conditional count within a single aggregate.

-- Demonstrating: CASE returns NULL for non-matches
SELECT customer_id,
       CASE WHEN status = 'COMPLETED' THEN 1 END AS completed_flag
FROM   orders
WHERE  customer_id = 1001;

 customer_id | completed_flag
-------------+---------------
        1001 |             1    ← COMPLETED: returns 1
        1001 |          NULL    ← CANCELLED: returns NULL

How is this different from the naive self-join or INTERSECT approach?

The naive approach runs two queries – one for completed, one for cancelled – and intersects them. Or it self-joins the table. Both scan the table multiple times.

-- Approach 1: INTERSECT (two table scans)
SELECT customer_id FROM orders WHERE status = 'COMPLETED'
INTERSECT
SELECT customer_id FROM orders WHERE status = 'CANCELLED';

-- Approach 2: Self-join (two table scans, may need DISTINCT)
SELECT DISTINCT a.customer_id
FROM   orders a
JOIN   orders b ON a.customer_id = b.customer_id
WHERE  a.status = 'COMPLETED'
  AND  b.status = 'CANCELLED';

-- Approach 3: Conditional aggregation (one table scan)
SELECT customer_id
FROM   orders
GROUP BY customer_id
HAVING COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END) > 0
   AND COUNT(CASE WHEN status = 'CANCELLED' THEN 1 END) > 0;

| Aspect | Conditional Aggregation | Self-Join | INTERSECT |
|---|---|---|---|
| Table scans | 1 | 2 | 2 |
| Duplicates | No | May need DISTINCT | Auto-deduplicated |
| Per-state counts | Yes, in SELECT | No | No |
| Extensibility | Add more CASE | Add more joins | Add more queries |

How do you adapt the pattern for different conditions?

The pattern generalizes to any combination of state requirements. Change the HAVING conditions to match the business rule.

-- A but NOT B: completed orders only, no cancellations
SELECT customer_id
FROM   orders
GROUP BY customer_id
HAVING COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END) > 0
   AND COUNT(CASE WHEN status = 'CANCELLED' THEN 1 END) = 0;

-- All of A, B, and C
SELECT customer_id
FROM   orders
GROUP BY customer_id
HAVING COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END) > 0
   AND COUNT(CASE WHEN status = 'CANCELLED' THEN 1 END) > 0
   AND COUNT(CASE WHEN status = 'REFUNDED'  THEN 1 END) > 0;

-- At least 2 out of 3 states (using a sum of existence flags)
SELECT customer_id
FROM   orders
GROUP BY customer_id
HAVING (
    CASE WHEN COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END) > 0 THEN 1 ELSE 0 END +
    CASE WHEN COUNT(CASE WHEN status = 'CANCELLED' THEN 1 END) > 0 THEN 1 ELSE 0 END +
    CASE WHEN COUNT(CASE WHEN status = 'REFUNDED'  THEN 1 END) > 0 THEN 1 ELSE 0 END
) >= 2;

How do you also report the per-state counts?

Project the conditional counts in the SELECT to show how many of each status a customer has. This is something the self-join approach cannot do in one shot.

SELECT customer_id,
       COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END) AS completed_cnt,
       COUNT(CASE WHEN status = 'CANCELLED' THEN 1 END) AS cancelled_cnt
FROM   orders
GROUP BY customer_id
HAVING COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END) > 0
   AND COUNT(CASE WHEN status = 'CANCELLED' THEN 1 END) > 0;

 customer_id | completed_cnt | cancelled_cnt
-------------+---------------+--------------
        1001 |             1 |             1

What about PostgreSQL's FILTER clause?

PostgreSQL supports the FILTER (WHERE ...) clause, which is a modern standard-SQL alternative that reads more clearly than the CASE expression.

-- PostgreSQL: FILTER clause
SELECT customer_id
FROM   orders
GROUP BY customer_id
HAVING COUNT(*) FILTER (WHERE status = 'COMPLETED') > 0
   AND COUNT(*) FILTER (WHERE status = 'CANCELLED') > 0;

What are common mistakes?

The most common mistake is using ELSE 0 with COUNT instead of SUM. With COUNT, the ELSE 0 means non-matching rows return 0 instead of NULL, and COUNT(0) is not zero – it counts the 0 as a value.

-- WRONG: ELSE 0 with COUNT counts ALL rows
SELECT customer_id,
       COUNT(CASE WHEN status = 'COMPLETED' THEN 1 ELSE 0 END) AS wrong
FROM   orders
GROUP BY customer_id;
-- wrong = total rows, not just completed ones

-- CORRECT: no ELSE with COUNT (NULL is ignored)
SELECT customer_id,
       COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END) AS correct
FROM   orders
GROUP BY customer_id;

-- ALSO CORRECT: ELSE 0 with SUM
SELECT customer_id,
       SUM(CASE WHEN status = 'COMPLETED' THEN 1 ELSE 0 END) AS correct
FROM   orders
GROUP BY customer_id;

What should a senior engineer know?

• An index on (customer_id, status) supports both the grouping and the conditional filters, making this a covering index.
• For existence-only checks, MAX(CASE WHEN cond THEN 1 ELSE 0 END) can short-circuit after finding one match.
• Status values should come from an enum or lookup table so the CASE conditions stay reliable and typo-free.
• This pattern is the foundation for building flexible segmentation queries: "customers who bought product A and also bought product B" in a single scan.

What are common interview follow-up questions?

• How do you find customers who have ONLY state A and no other states? (HAVING COUNT(CASE WHEN status = 'A' THEN 1 END) > 0 AND COUNT(CASE WHEN status <> 'A' THEN 1 END) = 0.)
• Can you use this with SUM instead of COUNT to check amounts? (Yes: HAVING SUM(CASE WHEN status = 'COMPLETED' THEN amount ELSE 0 END) > 100000.)
• How does this perform compared to EXISTS subqueries? (Conditional aggregation scans once; two EXISTS subqueries may scan twice, though the optimizer can sometimes merge them.)