skip to content

You want only the orders whose customer id appears in a flagged-accounts list, with no columns added: which operation, and why not an ordinary match?

level: middleimportance: should knowfreq 50%

answer

  1. this is a membership test, not a combination
  2. no columns added, so no collisions
  3. count can only fall, never grow
  4. distinct key values before testing membership

basics

~20 s

Use the keep-if-partnered filter: a match used purely as a membership test, where rows are kept or dropped by whether a partner exists and no columns are added. An ordinary widening match adds the other table's columns and can change the row count.

solid answer

~50 s

What you are describing is a **keep-if-partnered filter** — a match used purely as a membership test, where rows of one table survive according to whether a partner exists and the column set is untouched. Its mirror, the keep-if-unpartnered filter, keeps the rows that have no partner. Both guarantee two things an ordinary match cannot: the output is a subset of the orders table's rows, and its columns are exactly the orders table's columns. A widening match breaks both. It appends the flagged list's columns, which can collide with same-named columns you already have, and if a customer id occurs more than once on the flagged list it returns one output row per pairing, so the count grows. Some tools have a named verb for the filtering form; where there is none, test membership against the distinct key values of the other table.

go deeper

for a junior

Know that you can use one table to filter another by key membership without bringing any of its columns across, and that the two directions are keep-the-rows-that-have-a-partner and keep-the-rows-that-do-not.

for a middle

Explain the two guarantees the filtering form gives you — the column set is untouched and the row count cannot grow — and show why a widening match gives neither when a key value repeats on the consulted side.

for a senior

Build the filtering form from distinct key values where the tool offers no verb for it, assert that the row count did not increase, and use the unpartnered form up front to size how many rows a partnered-only match would have dropped.

for a principal

Decide whether membership tests in your pipelines are written as filters or as matches with columns discarded afterwards. The second reads as the same intent and fails differently, and letting both styles coexist means every review starts by working out which one it is looking at.

## What you actually asked for Read the requirement literally: *keep the orders whose customer id appears in a flagged-accounts list, with no columns added*. That is a question about **membership**, not about combining information. The flagged list is being used as a set of key values and nothing else — you want none of its columns in the answer. The operation for that is the **keep-if-partnered filter**: a match used purely as a membership test, in which rows of one table are kept or dropped by whether a partner exists on the other side, and no columns are added. Its mirror is the **keep-if-unpartnered filter**, which keeps exactly the rows that found nothing — the natural way to ask "which orders are from customers we have never seen?". ## Why the ordinary widening match is the wrong instrument A widening match is designed to bring the other table's columns along, and both consequences of that are unwanted here: | Property | Keep-if-partnered filter | Widening match kept to partnered rows only | |---|---|---| | Columns in the result | exactly the orders table's | plus every column of the flagged list | | Name collisions possible | no | yes, on any non-key column named the same on both sides | | Output row count | at most the input's | one row per pairing, so it can exceed the input's | | Result is a subset of the input | guaranteed | only when the key is unique on the other side | The row-count line is the one that bites. If the flagged list holds a customer identifier twice — two flag records for the same account, say, which is entirely ordinary in a list nobody deduplicated — then an order for that customer comes back twice from a widening match. Your "filter" has now inflated the very thing you were filtering, and every count and total computed afterwards is wrong in a direction that looks plausible. The filtering form cannot do this: its output is a selection of input rows, so the count can only stay the same or fall. ## The two routes when your tool has no verb for it This is a genuine split across the family, and a good answer names it rather than assuming one design: - **Where a named filtering verb pair exists**, it is one call, it says what you mean, and the guarantees above are properties of the operation rather than things you have to check. - **Where there is none**, build the test yourself: take the **distinct** key values of the flagged list and keep the order rows whose key is one of them. Taking distinct values first is the whole point — it is what stops the repeat problem before it starts. - **A third route** exists and is worth knowing but is worse: run a whole-keeping match, ask for a provenance marker, filter on it, then drop the columns the match added. It reaches the same place in four steps instead of one and can still inflate in the middle, so prefer it only when you also need something the match produced. ## Practical points that separate a good answer 1. **Say which table you are filtering.** The filtering form is asymmetric: the orders table is the thing being kept or dropped, and the flagged list is being consulted. Swapping them answers a completely different question. 2. **Distinct first, always.** Whichever route you take, the key values you test against should be the distinct ones. It is a one-word habit that removes an entire class of inflation. 3. **Compare the counts.** Record the row count before and after. For a filtering form the only legitimate outcomes are equal or fewer, so a larger number is immediate proof you reached for the wrong operation. 4. **Watch the absent keys.** Order rows whose key is itself absent will not be flagged as partnered under most designs, and whether they belong in the kept or the dropped set is a decision you should make deliberately rather than inherit. ## The mirror form, and why it is the one people forget The keep-if-unpartnered filter is how you answer "which rows have no counterpart?" — new customers missing from a reference extract, transactions with no matching account, identifiers a downstream system has never been told about. It is also the honest way to size a problem before choosing a match shape: run it first, count what it returns, and you know exactly how many rows a partnered-only match would have silently discarded. Reaching for it up front turns an invisible loss into a number you can put in a message to whoever owns the reference data.

  • How would you answer the opposite question — orders from customers who are not on the list?
    With the keep-if-unpartnered filter: keep exactly the rows that found no partner, again with no columns added. Where the tool has no verb for it, keep the order rows whose key is not among the distinct key values of the other table. Decide explicitly what happens to order rows whose key is itself absent, since they are neither clearly partnered nor clearly unpartnered.
  • Why does taking distinct key values first matter so much?
    Because a membership test cares only whether a value is present, while a widening match pairs with every occurrence of it. Reducing the other side to its distinct key values makes the two behave the same and removes any possibility of one input row becoming several. It costs one extra step and eliminates the commonest way a filter silently inflates a table.
  • When is the widening match the right call after all?
    When you actually need the other table's columns in the result — the flag reason, the date it was applied, the reviewer. Then the widening match is the correct instrument and the work moves to choosing the shape, resolving any same-named non-key columns before the call, and predicting the row count instead of assuming it stays put.

saying these in an interview costs you the question

  • Reaches for a widening match and then drops the extra columns afterwards
  • Assumes a filter cannot possibly increase the row count, whatever operation implements it
  • Tests membership against the raw key column instead of its distinct values
  • Believes every tool has a dedicated verb for the filtering form
  • Cannot say which of the two tables is the one being filtered