DoorDash Data Analyst Interview Preparation Guide - Mid Level

Direct answer

Go to the colleague privately first, before doing anything more public, and frame the concern as a question about the sampling method rather than a conclusion about their competence. Bring the specific evidence, propose a joint, falsifiable check that would settle whether the bias is real, and only then decide together how to handle the already-circulated report.

Structured elaboration

1. Verify before you raise it. Confirm the specific gap yourself (which source, what kind of gap) so you are not escalating a hunch. Raising a vague suspicion is more likely to read as an attack than raising a concrete, checkable one.
2. Private channel first. Do not raise it in the stakeholder meeting or a public thread. The goal at this stage is a shared understanding between the two of you, not a public correction.
3. Lead with evidence, not the conclusion. Ask how the sample was chosen and show what you noticed, rather than opening with "your report is biased." The evidence does the work; you are not the one delivering a verdict.
4. Propose a joint, falsifiable test. Agree in advance on a specific check that would settle the question either way, for example, re-running the analysis with a more complete data source and comparing results. If the two produce materially different conclusions, that is evidence of the bias; if not, the original report holds and nothing was lost.
5. Handle the stakeholder-facing correction together. If the test confirms the bias, present the fix as a normal part of the quality process, credit the colleague's original work, and avoid framing it as catching an error.

Worked example

A colleague circulated a cohort analysis to stakeholders built from a single data source you know has intermittent collection gaps. Rather than flagging it in the stakeholder thread, you ask to talk privately: "I noticed this cohort uses source A, do you know if that source had full coverage this quarter?" You show the specific evidence (gap periods, affected date ranges) and ask how the sample was chosen. Together you agree on the joint test: re-run the same cohort analysis using a second, more complete source and compare the two results. If the numbers move meaningfully, you have confirmed a real bias and both go to stakeholders together with an updated report and a data-quality caveat; if the numbers hold steady, the original report stands and the check cost an afternoon, not a reputation.

Trade-offs & pitfalls

• Raising it directly in the stakeholder meeting "to protect the org from a bad decision" scores a point in the moment but damages the working relationship and makes the colleague defensive on the next collaboration.
• Staying silent because raising it feels confrontational lets a real bias ship into decisions uncorrected, which is a worse outcome for the partnership than a slightly awkward private conversation.
• The senior move here is designing the joint test so the evidence settles the question, rather than relying on how persuasively you phrase the concern. A well-chosen test does the convincing; the conversation itself does not have to.
• A remaining pitfall: proposing a fix without proposing how to verify it worked. A joint investigation that ends without a joint, agreed check on the outcome tends to resurface as the same disagreement later.

Direct answer: Pick the tool by matching its physical behavior to what the KPI (key performance indicator) actually needs: a common table expression (CTE, a named subquery written with WITH ... AS (...)) for a one-off analysis you'll run once and throw away, a temp or staging table when the calculation is expensive but the reuse window is short (a single session, a single ETL run, meaning extract-transform-load), and a materialized view (a query whose result is physically stored on disk and refreshed on a schedule or trigger, rather than recomputed on every read) once the same expensive logic is read repeatedly by multiple dashboards and can tolerate being slightly stale. Near-real-time dashboards are the one case where none of the "precompute it" options fit cleanly: they usually need a lean, indexable live query instead, or an incrementally-updated summary table rather than a full materialization.

Structured elaboration

Recommendations by scenario:

• One-off ad hoc analysis: a CTE (or a plain subquery). There is no second reader to amortize setup cost against, so the fastest path to an answer wins over any persistence machinery.
• Repeatedly-used expensive calculation: a materialized view (or, where the engine lacks native materialized views, a scheduled job that populates a plain table). The cost of computing it is paid once per refresh instead of once per dashboard load, and it becomes indexable, which a CTE never is.
• Near-real-time dashboard: avoid full materialization; either query the base tables directly with supporting indexes so the planner can push filters down, or maintain an incrementally-updated summary table (updated on write, not recomputed wholesale) if the underlying computation is too heavy to run live on every page load.

When does a CTE pipeline graduate to a materialized view? Three independent signals, any one of which is usually enough on its own: (1) reuse -- the same CTE logic is being copy-pasted into a second, third, or fourth query or dashboard, which is a sign the definition should live in one place instead of many; (2) performance -- the CTE's underlying computation starts showing up as the dominant cost in an EXPLAIN plan across multiple callers, so the aggregate cost of recomputing it everywhere now exceeds the cost of maintaining a refreshed copy; (3) governance -- different query authors start writing slightly different filters around a copy-pasted CTE, so the "same" KPI silently drifts between dashboards. Any of these is the point to promote the logic into a materialized view (or persisted table) with one refresh schedule and one definition that every consumer reads.

Worked example

A KPI like "monthly active accounts" (distinct accounts with at least one qualifying event in the trailing 30 days) is a good stand-in: it is expensive on a large events table because it needs a distinct count over a rolling window, and it is exactly the kind of metric multiple dashboards want to show.

-- (a) one-off exploration: CTE, thrown away after this single query
WITH active_accounts AS (
  SELECT DISTINCT account_id
  FROM events
  WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT COUNT(*) AS mau FROM active_accounts;

-- (b) repeatedly-used, refresh-tolerant: materialized view, indexed, refreshed nightly
CREATE MATERIALIZED VIEW mv_monthly_active_accounts AS
SELECT account_id, MAX(event_time) AS last_active_at
FROM events
WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY account_id;

CREATE INDEX idx_mv_maa_account ON mv_monthly_active_accounts(account_id);
-- refreshed on a schedule, e.g. REFRESH MATERIALIZED VIEW mv_monthly_active_accounts;

-- (c) near-real-time: query live, relying on an index on events(account_id, event_time)
-- rather than pre-aggregating, so results are current to the second
SELECT COUNT(DISTINCT account_id) AS mau_live
FROM events
WHERE event_time >= NOW() - INTERVAL '30 days';

ML (machine learning) feature pipeline framing: this exact decision tree also applies when the "dashboard" is actually a model training job reading a feature. A feature explored once in a notebook is a CTE; a feature reused across many training runs (and needing point-in-time correctness, i.e. only using data available as of each label's timestamp) is exactly the "repeatedly-used expensive calculation" case, and belongs in a materialized feature table (or a feature store) that is refreshed on a known cadence, not recomputed inline in every training query. The freshness criterion becomes sharper here: a stale feature table used for training is often fine, but the same staleness at serving/inference time can silently create training-serving skew, so the acceptable staleness window has to be decided per use case, not assumed.

Spark SQL / Catalyst optimizer angle: the reuse math is different in Spark. Spark's Catalyst optimizer treats a WITH clause referenced multiple times by inlining and re-planning the underlying logical plan at each reference site by default, rather than computing it once and sharing the result the way a materialized view would. A CTE joined against three times in one Spark SQL query can trigger the same expensive computation three separate times unless you explicitly force sharing (persisting the intermediate DataFrame with .cache()/.persist(), or writing it out and reading it back). This makes the "graduate to materialization" decision arrive sooner in Spark than in a single-node warehouse: a CTE reused even twice within one query is worth checking for repeated work, not just a CTE reused across many separate queries.

Trade-offs & pitfalls

The common wrong turn is reaching for a materialized view purely because a query is slow, without checking whether the consumer can actually tolerate the staleness that comes with it: a materialized view refreshed hourly is the wrong answer for a dashboard that promises "as of right now." Conversely, leaving an expensive, frequently-reused CTE unmaterialized "for simplicity" quietly multiplies its cost by however many dashboards call it, since nothing about a CTE shares work across separate queries. Temp/staging tables sit in between and are easy to over- or under-use: they are the right tool inside a single multi-step job (build once, query several times, then discard), but reaching for one to serve a dashboard means re-inventing a materialized view's refresh logic by hand, usually worse. Always confirm the actual freshness requirement with the stakeholder before choosing: "near-real-time" and "updated every 15 minutes" are very different engineering problems that get conflated in casual requirements language.

Direct answer

The "so what" test means checking that a finding is tied to a decision or action the reader can actually take, not just a statistic. Before you include a finding, ask "if I were the recipient, what would I do differently after hearing this?" If the honest answer is nothing, you cut it, reframe it around the decision it does inform, or dig one level deeper until you reach the implication that matters to that audience.

Structured elaboration

• Identify the decision-maker's actual decision. A number only matters if it changes what someone chooses to do next.
• Connect the metric to a lever they control. If the reader can't act on the number, restate it in terms of something they can influence.
• State the implication before the number. Lead with what it means, then support it with the figure, not the other way around.

Worked example

A report says "weekly active users dropped from 52% to 48% after the redesign." On its own that fails the so-what test, it is just a fact. Reframed: the drop is 4 percentage points off a base of 52%, which is about 1 in 13 of the users who used to come back weekly (4/52 is roughly 7.7%, close to 1/13). The reframed version adds that the drop is concentrated in first-week users, so the implication is "fix onboarding before rolling this out further," which is something the team can act on immediately.

Trade-offs and pitfalls

Forcing every finding into an action can lead to over-editorializing or manufacturing false urgency around numbers that are legitimately just monitoring metrics. Not everything needs a call to action; some findings are correctly filed as "keep watching this."

What the interviewer probes next

Expect a follow-up about findings that are genuinely informational only, and how you avoid crying wolf by forcing an action onto every number you report.

For a new recommendation engine, the primary metrics should directly measure whether the recommendations are being acted on and monetized, while supporting metrics catch whether that lift is coming at the expense of user trust or content diversity.

Metric set

Perverse incentive to watch for

Optimizing purely for CTR rewards recommending sensational, clickbait-adjacent, or already-popular items rather than genuinely useful ones, since a system can raise clicks by surfacing items the user was likely to buy anyway (cannibalizing organic discovery) or by choosing attention-grabbing but low-relevance items that get clicked once and never again. This shows up as CTR rising while post-click satisfaction signals (return visits to the same category, low bounce, revenue per click) stay flat or fall, so the supporting diversity and session-quality metrics exist specifically to catch this pattern before it's mistaken for a genuine win.

Trade-offs and pitfalls

A pure revenue-per-click primary metric alone can reward recommending expensive items over items the user actually wants, so pairing CTR with revenue (not either alone) keeps the incentive aligned with genuine relevance, not just monetary value per click.

Direct answer

With $n = 50$, sample mean $\bar x = 20$, and sample standard deviation $s = 4$, use the t-distribution because $s$ is an estimate of the population standard deviation, not a known value. That gives a 95% confidence interval of approximately $(18.86,\ 21.14)$.

Structured elaboration

Why t, not z. The choice between z and t is about whether the population standard deviation $\sigma$ is known, not really about whether $n$ crosses some threshold like 30. Here only the sample standard deviation $s$ is given, so $\sigma$ is unknown and must be estimated from the data, which is exactly the situation the t-distribution accounts for: its slightly heavier tails reflect the added uncertainty of estimating $\sigma$ from a finite sample, using $df = n - 1$ degrees of freedom. As $n$ grows, $t_{n-1}$ converges to $z$, which is why the common "z if n≥30" shortcut works in practice, but the underlying reason is the known-vs-estimated-variance distinction, not sample size on its own.

Assumptions. The 50 measurements are independent draws, and either the underlying population is approximately normal, or $n=50$ is large enough for the Central Limit Theorem to make the sampling distribution of the mean approximately normal even if the raw data isn't. With $n=50$ that's a reasonable assumption for most metrics unless the underlying distribution is severely skewed or has extreme outliers, in which case a bootstrap CI is a safer check.

Worked example

Critical value at $df = 49$: $t_{0.975,49} \approx 2.0096$ (verified with scipy.stats.t.ppf(0.975, 49)).

For comparison, using $z = 1.96$ instead gives a margin of $1.96 \times 0.5657 \approx 1.109$, or $(18.89, 21.11)$, a difference of about 0.03 in the margin. At $n=50$ the t and z answers are already nearly indistinguishable, but the t-based interval is the formally correct one since $\sigma$ was never given.

Trade-offs & pitfalls

• Reporting the z-based interval here isn't a big numeric error at $n=50$ (the two margins differ by under 3%), but it's the wrong justification: "n is big enough" isn't really why t is correct or incorrect, "is $\sigma$ known" is. Getting the reasoning right matters more than the ~0.03 numeric difference in this case, and matters a lot more at smaller $n$ where the gap between t and z widens substantially.
• If the 50 measurements aren't actually independent (e.g. repeated measurements on the same handful of subjects), both formulas understate the true uncertainty regardless of which critical value is used.
• If the underlying distribution is heavily skewed rather than roughly symmetric, a mean-based CI can be misleading even with $n=50$; a bootstrap interval or a transformation (e.g. log) is worth checking as a sanity check before reporting the analytic interval as-is.

Situation: I worked closely with a partner who was strong technically but often changed direction late, which was creating churn for the rest of the team.

Task: I needed to give difficult feedback without damaging trust.

Action: I chose a private conversation and used the SBI format, which means Situation, Behavior, Impact. I said, "In yesterday's planning meeting, when the scope changed after we had already aligned with design, it created rework and made the team less confident in the plan." I kept the tone factual, then asked what was driving the change. It turned out they were reacting to pressure from another stakeholder and had not surfaced it earlier. We agreed they would flag uncertainty sooner and bring changes through planning instead of in the middle of execution.

Result: The conversation was uncomfortable, but it improved our working relationship because it was specific and fair. Their behavior became more predictable, and the team trusted them more because expectations were clearer.

Direct answer

The null hypothesis in a conversion experiment is the default assumption that the treatment has NO effect on conversion, that any observed difference between control and treatment is just sampling noise. A Type I error is falsely rejecting that null (concluding the treatment worked when it actually did not); a Type II error is falsely failing to reject it (concluding the treatment did not work when it actually did). Product experiments typically accept a 5% Type I error rate ($\alpha = 0.05$) and a 20% Type II error rate ($\beta = 0.20$, i.e. 80% power), because shipping a truly-neutral change is usually cheaper to recover from than missing a truly-positive one, but that default is a judgment call, not a law of nature.

Structured elaboration

Formal definitions. For a checkout-conversion A/B test, the null hypothesis $H_0$ is that the treatment's true conversion rate equals the control's true conversion rate:

The alternative hypothesis $H_1$ is that they differ: $H_1: p_{\text{treatment}} \neq p_{\text{control}}$ (a two-sided test; a one-sided test would specify a direction). The test computes a p-value, the probability of seeing a difference at least as extreme as what was observed, ASSUMING $H_0$ is true; if that p-value falls below $\alpha$, the test rejects $H_0$ and calls the result "statistically significant."

The two error types, side by side:

Why the asymmetry in typical defaults. $\alpha = 0.05$ and power $= 0.80$ ($\beta = 0.20$) is a widely used default, but it is deliberately asymmetric: it accepts a 1-in-4 chance of MISSING a real effect for every 1-in-20 chance of falsely CLAIMING one. That asymmetry reflects a judgment about which error is more costly in a typical product context: a false positive (Type I) means shipping a change that does nothing, which mostly just wastes the engineering effort already spent and adds a small amount of code complexity; a false negative (Type II) means walking away from a genuinely valuable change, which is a real, if invisible, opportunity cost. Tightening $\alpha$ (say to 0.01) reduces false positives but, holding sample size fixed, increases the false-negative rate, since the two trade off against each other for a given amount of data; the only way to improve BOTH simultaneously is to collect more data.

Worked example

Type I error example, checkout-conversion test. A team tests a simplified checkout form against the current one. The true underlying effect is exactly zero (the simplification looks nicer but genuinely does not change behavior), but due to random sampling variation the treatment group happens to show 200 more purchases than control would have by chance alone; the p-value comes in at 0.03, below the 0.05 threshold, so the team declares a win and ships the change. The null hypothesis was actually TRUE, but the test rejected it: this is a Type I error, a false positive. Cost: engineering effort spent building and maintaining a change that provides no real business value, plus a wrong entry in the "what we learned" experimentation log that could mislead a future decision.

Type II error example, same test. Now suppose the simplified checkout genuinely does increase conversion by a real 1.5 percentage points, but the test only ran for 3 days with modest traffic, well under the sample size the effect size would need for adequate power. The observed difference is directionally positive but the p-value comes in at 0.18, above 0.05, so the team concludes "no significant effect" and reverts the change. The null hypothesis was actually FALSE (a real effect existed), but the test failed to reject it: this is a Type II error, a false negative. Cost: a genuinely valuable improvement gets shelved, and unless someone re-tests it later with adequate power, that revenue lift is simply never captured.

Acceptable alpha/beta trade-offs in practice. The "standard" 0.05/0.20 defaults are a reasonable starting point, not a universal rule; the right values depend on the DECISION's cost asymmetry. A change with very low downside risk and high potential upside (a copy tweak with negligible engineering cost) can tolerate a looser $\alpha$, since a false positive there is nearly free to walk back. A change with real downside risk (altering how payment information is collected, where a false positive could mean shipping something that quietly increases fraud) warrants a STRICTER $\alpha$ (0.01), accepting the corresponding increase in Type II risk, because the cost of a false positive here is materially higher than the cost of a missed opportunity. The right practice is to name the alpha/beta trade-off explicitly as a business decision before running the test, not to default to 0.05/0.20 by habit regardless of what is actually being tested.

Trade-offs and pitfalls

• Common mistake: treating "not statistically significant" as proof the treatment has NO effect. Failing to reject $H_0$ only means the test could not distinguish the observed data from pure noise at the chosen confidence level; it is equally consistent with "no effect" and with "an effect too small or the sample too underpowered to detect," exactly the Type II scenario above.
• Peeking inflates the real Type I rate above the stated $\alpha$. Checking results daily and stopping the moment the p-value first dips below 0.05 (rather than committing to a pre-determined sample size or using a sequential-testing method designed for repeated looks) means the ACTUAL false-positive rate across the whole monitoring period is higher than the nominal 5%, since you are effectively giving the test many chances to cross the threshold by chance.
• A statistically significant result is not automatically a practically significant one. With enough traffic, even a genuinely tiny, business-irrelevant effect (a 0.05 percentage-point lift) can reach $p < 0.05$; the minimum detectable effect chosen before the test should reflect the smallest lift actually worth shipping, not just "anything nonzero."
• Power depends on the true effect size, which is unknown before the test runs. A power calculation is only as good as its assumed effect size; if the real effect turns out smaller than assumed, the test is actually underpowered relative to what was planned, which is a common, quiet source of Type II errors that a team never notices because the test "ran as planned" on paper.

Direct answer
When sales leaders dispute a statistical forecast, the right response is a structured process, not a unilateral defense of the model or a silent capitulation: validate the underlying data first, surface and reconcile the specific assumptions being disputed, produce side-by-side scenarios so both views are visible rather than argued in the abstract, propose a documented compromise where warranted, and use the resolution to rebuild trust for future cycles rather than treating it as a one-off argument to win.

Structured elaboration

• Validating the data: before engaging on the substance of the disagreement, confirm the forecast's inputs are actually correct (no stale data, no broken pipeline, no missed recent event) - a meaningful fraction of forecast disputes turn out to be legitimate data-quality catches, not a genuine model-vs-judgment disagreement, so ruling this out first is both fast and often the actual resolution.
• Reconciling assumptions: identify precisely WHERE the statistical model and the sales leaders' intuition diverge - is it a different read on a specific deal, a market condition the model has no way to see (a competitor's recent move), or genuine overconfidence on one side? Naming the specific assumption in dispute turns a vague "the number feels wrong" into something concrete enough to actually resolve.
• Producing side-by-side scenarios: rather than picking one number, present the model's forecast AND a scenario reflecting the sales leaders' adjustment, with the specific assumption difference driving the gap made explicit - this respects both inputs and lets the eventual decision-maker see the trade-off rather than a black-box disagreement.
• Proposing compromise approaches: a blended forecast (weighting the statistical model and human judgment, as in collaborative forecasting more broadly) is often defensible when both sides have genuine signal the other lacks; document the blend and the reasoning, not just the final number.
• Restoring trust for future cycles: track which side (model or human adjustment) was closer to the eventual actual, and feed that back explicitly into the NEXT cycle's process - if human overrides have systematically been closer to actuals recently, that's a real signal the model may be missing something structural; if the model has been closer, that's useful evidence too, and either way, closing the loop with actual outcomes (rather than re-litigating the same disagreement from scratch each cycle) is what actually builds durable trust in the forecasting process.

Worked example
This is the same underlying discipline as any collaborative forecasting workflow where human adjustments (sales judgment, marketing input) are layered onto a statistical base: the productive version tracks whether those adjustments, on average, have actually improved on the model's own accuracy over time, rather than treating every override as automatically valuable or automatically suspect - a defensible process measures this explicitly (comparing overridden vs. non-overridden forecast accuracy against eventual actuals) instead of relying on anecdote or seniority to settle the disagreement.

Trade-offs & pitfalls
The most damaging failure mode here is NOT reconciling anything and instead quietly picking whichever number is more politically convenient in the moment - that erodes the forecasting process's credibility either way (with the modelers if judgment always wins with no accountability, or with the business side if the model always wins with no acknowledgment of real on-the-ground signal it can't see). A durable process needs to be able to point to a track record, not just a single episode, to settle these disputes over time.

Direct answer

Two variables moving together does not tell you which one, if either, is causing the other, or whether a third factor is driving both; the clearest way to show this is a case where the "obvious" causal story is provably wrong.

Structured elaboration

Two charts and captions that work well:

• Chart 1: ice cream sales and drowning incidents both rising over the summer months, captioned "these two rise and fall together" - purely descriptive, no causal claim.
• Chart 2: the same two series with a third line added, average daily temperature, captioned "both are driven by warm weather: more swimming AND more ice cream sales, not one causing the other" - revealing the shared driver (the classic confounder in this analogy).
  A short company policy that follows naturally: act directly on a correlated finding only when a plausible mechanism is already well understood and the cost of being wrong is low; require a controlled experiment before acting on any correlation that would drive a costly or hard-to-reverse decision.

Worked example

A live version of the same pattern: if a company observes that customers who use a certain feature have higher retention, it's tempting to conclude "the feature causes retention." Retention-prone customers (more engaged from the start) may simply be more likely to try new features too, making feature usage the ice cream and underlying engagement the temperature. Only a randomized rollout of the feature, comparing retention between an offered group and a not-offered group, can distinguish "the feature helps" from "engaged people use both features and retain better regardless."

Trade-offs and pitfalls

In the moment, when a stakeholder in a live meeting jumps straight from a correlation to a policy change, the useful move is not to lecture on causation abstractly but to ask one concrete question: "what's a plausible reason these two might move together WITHOUT one causing the other?" That question does the work the ice-cream analogy does, without requiring the audience to sit through a definitions lesson, and it usually surfaces the confounder the audience hadn't considered, which is what actually changes their mind.

Each of these three snippets fails for a different reason, and the fix generalizes into one robust pattern for exclusion lists supplied by an application.

Structured elaboration

1. WHERE status IN () is a syntax error in most engines (or, in dialects that allow it, matches nothing) because an empty list has no values to compare against. There is no single "corrected" form independent of intent: if the empty list means "no restriction was requested", drop the IN clause entirely before the query is built; if it means "this filter should legitimately match nothing" (e.g. a UI multi-select with every option unchecked), the honest, portable equivalent is WHERE FALSE, valid everywhere and behaving consistently instead of relying on how a particular engine happens to parse an empty list.
2. WHERE discount = NULL is not a syntax error but always evaluates to UNKNOWN (never TRUE), because NULL represents "unknown value", and no comparison operator, including =, can determine two unknowns are equal. Correct form: WHERE discount IS NULL.
3. WHERE order_date BETWEEN '2024-03-01' AND is an incomplete statement (missing the upper bound) and is a syntax error. Correct form, supplying the missing bound: WHERE order_date BETWEEN '2024-03-01' AND '2024-03-31'.

Generalizing to a parameterized exclusion list: an application variable :excluded_statuses might arrive as NULL (no filter intended) or as an empty array (filter to nothing). Two safe patterns:

-- NOT IN, guarded against NULL and empty
WHERE (:excluded_statuses IS NULL OR status NOT IN (SELECT UNNEST(:excluded_statuses)))
-- NOT EXISTS, the more robust default (see Trade-offs)
WHERE NOT EXISTS (
  SELECT 1 FROM UNNEST(:excluded_statuses) AS excl(status_val)
  WHERE excl.status_val = status
)

(UNNEST expands an array value into one row per element, so an array parameter can be compared against, or joined to, row by row, the same way a real table would be.) Checking for NULL up front in the NOT IN version means "no exclusions" doesn't accidentally collapse to NOT IN (), which some engines error on and others silently treat as "exclude nothing" or "exclude everything" depending on the engine, an inconsistency worth not relying on. The NOT EXISTS version doesn't need that guard at all: if :excluded_statuses is an empty array, UNNEST produces zero rows, NOT EXISTS over zero rows is always TRUE, and every order is correctly kept, no special-casing required.

The reason NOT EXISTS doesn't need the guard, and NOT IN does, is the NULL-poisoning problem itself: if :excluded_statuses contains a NULL element (say, a bad value slipped in from the application), status NOT IN (SELECT UNNEST(:excluded_statuses)) compares status against that NULL for every row, and status <> NULL evaluates to UNKNOWN. NOT IN's OR-chain of comparisons needs every single comparison to be definitively TRUE to include a row; one UNKNOWN in the chain poisons the whole thing to UNKNOWN (never TRUE), so the query silently returns ZERO rows, for every row, not just ones that would have matched the NULL. NOT EXISTS never has this problem, because it only ever asks "does at least one matching row exist", a question a NULL row can fail to answer TRUE to without poisoning anything else in the chain.

Worked example

Given orders(1,'shipped'), (2,'cancelled'), (3,'pending') and an exclusion list of ('cancelled'): the NOT EXISTS-based exclusion, WHERE NOT EXISTS (SELECT 1 FROM UNNEST(:excluded_statuses) AS excl(status_val) WHERE excl.status_val = status), correctly returns orders 1 and 3, excluding order 2 (status 'cancelled' matches the one excluded value).

With an EMPTY exclusion list (:excluded_statuses = []): UNNEST produces zero rows, so NOT EXISTS is TRUE for every order with no special-case needed, and the query correctly returns all three orders, 1, 2, and 3.

With a NULL slipped into the exclusion list (:excluded_statuses = ['cancelled', NULL]): NOT EXISTS still correctly returns orders 1 and 3, unaffected by the NULL element, since the NULL element simply never matches any row's status, it doesn't need to disprove anything for the other rows. Contrast this with the NOT IN version on the same NULL-containing list: status NOT IN ('cancelled', NULL) returns ZERO rows, silently dropping orders 1 and 3 too, not just excluding order 2, exactly the NULL-poisoning failure this whole pattern exists to avoid.

Trade-offs and pitfalls

The single most reliable fix across engines is NOT EXISTS rather than NOT IN, because NOT EXISTS never has the NULL-poisoning problem NOT IN has, demonstrated above: reach for it by default in exclusion-list logic. The NOT IN plus explicit NULL/empty-list guard shown above is still a legitimate pattern when a team already has NOT IN-based logic and wants the smallest possible diff, it just needs the guard NOT EXISTS gets for free.