Senior Business Intelligence Analyst Interview Preparation Guide - Lyft

Approach: convert records into a pandas DataFrame, normalize dates, filter transactions for the target month, compute total revenue per active user in that month, and return the mean (ARPU). Active user = any user with at least one transaction in the month.

import pandas as pd
from typing import List, Tuple, Union

def compute_arpu(transactions: List[Tuple[Union[int,str], float, str]], month: str) -> float:
    """
    transactions: list of (user_id, amount, occurred_at) where occurred_at is ISO date/time string
    month: target month in "YYYY-MM" format
    Returns: ARPU (float). Returns 0.0 if no active users or no transactions in month.
    """
    if not transactions:
        return 0.0

    df = pd.DataFrame(transactions, columns=["user_id", "amount", "occurred_at"])
    # parse dates robustly
    df["occurred_at"] = pd.to_datetime(df["occurred_at"], errors="coerce")
    df = df.dropna(subset=["occurred_at", "amount", "user_id"])

    # filter by month string YYYY-MM
    try:
        year, mon = map(int, month.split("-"))
    except Exception:
        raise ValueError("month must be 'YYYY-MM'")

    mask = (df["occurred_at"].dt.year == year) & (df["occurred_at"].dt.month == mon)
    df_month = df.loc[mask]

    if df_month.empty:
        return 0.0

    # total revenue per active user in month
    revenue_per_user = df_month.groupby("user_id")["amount"].sum()
    arpu = revenue_per_user.mean()
    return float(arpu)

Key points:

• Time complexity: O(n) to parse/filter/group where n = number of records.
• Space: O(n) for DataFrame.
  Edge cases handled:
• Empty input or no transactions in month -> returns 0.0
• Malformed dates are coerced to NaT and dropped
• Multiple transactions per user aggregated
• Negative/refund amounts included in net ARPU
• Invalid month format raises ValueError

Possible extensions: support timezone-aware parsing, accept month as datetime, or return ARPU alongside active user count and total revenue for dashboards.

Direct answer

For an online marketplace, the platform's own revenue is gross merchandise value (GMV, the total value of transactions flowing through the platform) multiplied by the take rate the platform keeps as commission; GMV itself decomposes into how many customers are active, what share convert, how much they spend, and how often. Each term in that chain has its own measurement approach and its own way of quietly going wrong.

Structured elaboration

$\text{Platform Revenue} = GMV \times \text{Take Rate}$
$GMV = N_{active} \times CR \times AOV \times F$

where $N_{active}$ is active customers, $CR$ is conversion rate, $AOV$ is average order value, and $F$ is purchase frequency over the period. Separating platform revenue from GMV matters specifically for a marketplace: the buyer's total spend and the platform's actual take are different numbers, and conflating them overstates what the business itself earns.

Worked example

Suppose, for one period: 10,000 active customers, a 4% conversion rate, $60 average order value, and 1.5 orders per active customer.

$GMV = 10{,}000 \times 0.04 \times \$60 \times 1.5$

Working left to right: $10{,}000 \times 0.04 = 400$ converting customers; $400 \times $60 = $24{,}000$; $$24{,}000 \times 1.5 = $36{,}000$.

$GMV = \$36{,}000$

If the platform's blended take rate on this category is 15%:

$\text{Platform Revenue} = \$36{,}000 \times 0.15 = \$5{,}400$

The buyers collectively spent $36,000 through the marketplace, but the platform itself only earned $5,400 of that; a decomposition that stops at GMV and calls it "revenue" overstates the business's actual earnings by more than six times in this example.

Trade-offs & pitfalls

The multiplicative decomposition assumes the four GMV terms move independently, but they often do not: a promotion that raises conversion rate frequently lowers average order value at the same time (customers converting on smaller, promo-priced baskets), so a healthy-looking conversion number can mask a shrinking GMV if AOV is not checked in the same breath. Take rate is the term most often left out of a customer-facing decomposition entirely, which is the core risk for a marketplace specifically: reporting buyer GMV growth as if it were platform revenue growth misrepresents the business, especially when take rate itself is drifting down due to a mix shift toward lower-commission categories or negotiated seller discounts. Finally, purchase frequency and active-customer counts both depend on identity resolution; if that resolution logic changes (a new login system, say), every term in the decomposition can shift for reasons that have nothing to do with real buyer or seller behavior.

A warehouse-level result cache, BigQuery's automatic result cache is the canonical example, stores the exact output of a previously executed query, keyed by the query's text (and, in most implementations, the state of the underlying tables at execution time), and serves that stored result instead of re-running the query the next time an identical query is issued, as long as none of the tables it read have changed since the cached result was produced.

How it interacts with table updates

The cache is invalidated per-query the moment any table the original query touched changes, and it separately carries its own upper-bound time-to-live independent of table changes: BigQuery's automatic result cache, for example, holds a cached result for roughly 24 hours and also invalidates it immediately if any referenced table changes before that window closes, whichever happens first. A new insert, update, or delete to any dependency invalidates every cached result that depended on that table, even a result that was otherwise perfectly fine, and even a result over tables that never change still eventually falls out of cache once the TTL elapses. This means result-cache freshness is exact (you will never see a result that is definitely stale relative to the table's current state) but its hit rate on a table that updates frequently, even in ways unrelated to what a specific dashboard displays, can be much lower than expected.

Designing to benefit from it

• Keep dashboard queries byte-for-byte identical across users and page loads where possible (same column order, same literal formatting, same filter representation), since most result-cache implementations key on the query text, not its semantic meaning, so two queries that return the same rows but are phrased differently will not share a cache entry.
• Separate frequently-updated tables from the tables a dashboard's expensive aggregation actually depends on, where the data model allows it, so that an unrelated update elsewhere in the warehouse does not needlessly invalidate a cached dashboard result.
• Combine with a materialized view or pre-aggregated table for the truly high-traffic dashboards, so the result cache is caching a cheap read of an already-small pre-aggregated table rather than a full scan-and-aggregate, which keeps even a cache miss relatively cheap.

Trade-offs and pitfalls

Relying on the result cache alone for freshness guarantees is fragile: it silently serves a fresh answer when nothing changed and silently falls back to a full, potentially expensive recomputation the instant anything changes, with no visible degradation warning to whoever is watching dashboard latency. Do not treat the cache hit rate as something you can directly control; design the query patterns and table update cadence around it instead, and treat it as a free accelerant on top of a materialized-view strategy, not a substitute for one.

1. Completed Trips (market activity - rider + driver)

• Improvement: up (more fulfilled demand)
• Guardrail: Cancellation Rate (rises may hide churn)
• Frequency: Weekly (with daily spike alerts)

2. Gross Bookings / Revenue per Week (monetary health)

• Improvement: up
• Guardrail: Average Trip Distance or Surge % (big revenue increase driven by longer trips or extreme surge may mask poor underlying demand)
• Frequency: Weekly (daily for anomalies)

3. Rider Conversion / Booking Rate (rider-side experience)

• Improvement: up (more riders who open app book a ride)
• Guardrail: App Crash Rate or Time-to-Match (UX issues can lower conversion)
• Frequency: Daily

4. Active Drivers / Driver Utilization (driver-side supply & engagement)

• Improvement: Active drivers stable/increasing and utilization (trips per active driver) healthy
• Guardrail: Driver Churn Rate or Acceptance Rate (low acceptance suggests dissatisfaction)
• Frequency: Daily

5. Marketplace Match Time / Time-to-Pickup (efficiency & experience)

• Improvement: down (shorter match-to-pickup time)
• Guardrail: Cancellation Rate by pickup window (faster matches but more cancellations may indicate bad matches)
• Frequency: Real-time/Hourly for ops, summarized weekly

6. Fill Rate / Unfulfilled Requests (%) (demand-supply balance)

• Improvement: down (fewer unfulfilled)
• Guardrail: Average Wait Time and Surge Frequency (low unfulfilled but very high surge could show tight supply)
• Frequency: Daily (real-time alerts when spike)

For leadership: deliver the six metrics on one page with trend sparklines, week-over-week % change, and one-sentence insight + recommended action.

Direct answer

Build a mapping and identity-resolution layer between the two companies' product and customer models, assign new conformed surrogate keys for the unified entities, keep each company's original dimension tables live and queryable during a transitional period (so pre-merger historical reports remain reproducible exactly as before), and cut fact tables over to the unified conformed dimensions once the mapping is validated.

Structured elaboration

• Mapping approach: similar to conforming a product dimension across multiple sources, build explicit mapping tables (companyA_customer_id -> conformed_customer_key, companyB_customer_id -> conformed_customer_key) using a combination of exact-match keys where available (shared tax ID, email) and fuzzy matching with human review for ambiguous cases.
• Surrogate key generation for the unified model: mint entirely new conformed surrogate keys (not reusing either company's original keys, which avoids collisions and signals clearly that this is the unified, post-merger identity space) for every matched or newly-recognized entity.
• Conformed attributes: define, for any attribute both companies tracked differently (customer segment definitions, product categorization), an explicit reconciliation rule or a documented mapping between the two taxonomies, rather than silently picking one company's definition.
• Transitional layer, running both models in parallel: keep each company's original schema and historical fact tables intact and queryable (so a pre-merger report, re-run during the transition, still reproduces its original numbers exactly), while NEW fact data (post-merger) loads against the unified conformed dimensions; a transitional reporting layer can present a blended view by translating old-company-specific keys through the mapping tables when needed.

Worked example

Company A's customer CA-500 and Company B's customer CB-900 are identified as the same real-world entity (matched via a shared corporate tax ID) and both map to a new conformed customer_key = 100001. Pre-merger sales history from both companies remains queryable against their ORIGINAL dimension tables for historical continuity; post-merger sales load against the new conformed customer_dim, and a cross-period trend report joins pre-merger history through the mapping table to the conformed key to show this customer's full relationship across the merger boundary.

Trade-offs and pitfalls

Rushing to force ALL historical data into the unified model immediately (rather than running the transitional dual-model period) risks corrupting the historical record if the identity-resolution mapping has errors discovered later; keeping the original per-company schemas alive and immutable during transition gives a safety net to fall back on, at the cost of running two schemas in parallel for a period, which is real but bounded operational overhead worth paying for a migration of this risk level.

Situation: Designing a Looker Explore for marketing where analysts need flexibility but business users need simplicity.

Approach / Patterns:

• Layered modeling: Expose a thin, well-documented Explore that joins canonical views; then create derived Explores or extension Explores for advanced analysts.
• Use persistent derived tables (PDTs) for expensive aggregations (e.g., session-level attribution) to keep Explore performant.
• Create curated “metric” views (semantic layer) that centralize calculations (e.g., conversions, CAC) so BI and dashboards reuse consistent logic.

LookML examples:

explore: marketing_events {
  label: "Marketing — Events"
  persist_for: "24 hours" # for PDTs
  always_filter: { field: events.is_active value: "yes" } # safety
  access_filter: { field: users.org_id user_attribute: "org_id" } # row-level
}

Field exposure rules:

• Default visible fields: business-friendly dimensions (campaign, channel, date) and a small set of validated metrics (impressions, clicks, conversions).
• Hidden technical fields by default (ids, raw JSON); mark as sql: hidden: yes and enable only in an “Advanced” user group.
• Use user_attribute-driven access_filters to enforce row-level security and limit data leaks.
• Create semantic aliases and descriptions for every field; include examples and suggested uses.

UI defaults and discoverability:

• Set user_friendly field labels, clear descriptions, and add tags (e.g., “PII”, “High-cost”) for searchability.
• Default sort and filters: pre-filter by recent date window (last 30/90 days) and default timezone; default limit rows to 10k.
• Provide templated Looks / Saved Looks: “Top Campaigns (Last 30 days)”, “Channel ROI” — as starting points.
• Use drill fields and curated join paths to guide exploration and prevent accidental cross-joins.

Performance & safety:

• Limit cross-database joins; warn in model docs when joins may cause row explosion.
• Add always_filter and access_filter for safety; use sql_always_where for costlier Explores.
• Monitor query performance and add cost-based logging; move heavy transforms to ETL/PDTs.

Result: Layered access and curated defaults let casual users get fast, correct answers while power users can opt into advanced fields and Explores — balancing discoverability, performance, and safety.

Direct answer

SLI, SLO, and SLA are three layers of the same idea, stated with increasing weight. An SLI (service level indicator) is what you actually measure. An SLO (service level objective) is the internal target you set for that measurement. An SLA (service level agreement) is the external promise, usually contractual, built on top of that target, with consequences if you miss it. In plain terms: the SLI is the speedometer, the SLO is the speed limit you've set for yourself, and the SLA is the speed limit you've promised a customer you won't exceed, with a penalty if you do.

Picking the example and the threshold

• Choose one measurable thing the executive already cares about, not an internal engineering metric they have no context for. "Percent of requests that succeed" beats a raw latency percentile for this audience, because success or failure needs no further explanation.
• State the SLO as a number deliberately below what looks achievable. This is the part executives most often misread: an SLO of 99.9% isn't "we're at 100% and slipping a little," it's a chosen buffer that leaves room to ship changes and absorb normal failures without over-investing in reliability nobody needs.
• The SLA number sits below the SLO, with a consequence attached, and that gap is itself worth explaining: it exists so that missing the internal target doesn't automatically mean breaking a customer promise.
• The same three-layer structure holds outside web services too, whether you're onboarding a new product manager on a team's SLOs for the first time or defining an SLI/SLO for a streaming data pipeline (there the SLI might be how stale the data is, instead of whether a request succeeded), the relationship between the three layers doesn't change, only what's being measured does.

Worked example

Say the team sets it up this way for a checkout API. SLI: percent of checkout requests that return successfully within two seconds. SLO: 99.9% of checkout requests meet that bar, measured over a rolling 30 days, the number engineering is held to internally. SLA: 99.5% of checkout requests meet that bar, measured monthly, written into the enterprise customer contract; falling below it triggers a service credit specified in the contract.

Business consequence of missing the SLA (say the month comes in at 99.3%): this is a contractual breach. The customer is owed the agreed credit, and depending on the contract, may have grounds to escalate or walk away. It's a direct, quantifiable cost and a trust hit that shows up outside engineering entirely.

Business consequence of exceeding the SLO (say the month comes in at 99.97% against a 99.9% target): this isn't a "consequence" in the SLA sense, it's a signal. Consistently beating the SLO by a wide margin means either the target is stale and could absorb more risk (ship faster, take on more ambitious changes), or the team is over-investing effort in reliability the product doesn't need. Either way it's a prompt to revisit the number, not something to report as a win on its own.

Trade-offs and pitfalls

The most common executive misunderstanding is treating the SLO as the promise, when the SLA is the promise and the SLO is the internal cushion above it. Say that gap out loud every time, or the SLO number will get quoted externally by mistake. The second pitfall is picking a metric that's technically correct but means nothing to the audience, an uptime percentage without saying what "down" costs the business, always translate the metric into what the customer actually experiences before attaching a number to it.

Direct answer
Mentoring, coaching, sponsorship, and management are four distinct levers, distinguished mainly by time horizon and mechanism: mentoring shares knowledge and context over a long relationship, coaching targets a specific skill or behavior over a shorter window, sponsorship uses your own influence and credibility to open doors the person can't open themselves, and management is the formal, ongoing accountability for someone's performance and direction. Most people need some mix of all four at different times, not just one.

Structured elaboration

The four levers compared

How to decide which to use

The fastest diagnostic is asking what's actually limiting the person right now: if it's a skill they don't have, that's coaching; if it's broad judgment or context that only comes with time and exposure, that's mentoring; if the person is already capable but not getting the opportunities to prove it, that's sponsorship, and it's the one lever the person genuinely cannot apply to themselves, since it depends on someone else's credibility, not their own effort.

Making it concrete, not just definitional

A strong answer doesn't stop at the definitions; it attaches a measurable outcome and a short plan to each one for a specific person. For example: coaching a specific gap in written communication might target "clear, well-structured design docs reviewed without major restructuring" within a defined window; sponsorship for a strong, under-recognized performer might target getting their name into a specific promotion or staffing conversation they wouldn't otherwise be part of. Naming the outcome is what separates "I know the definitions" from "I actually apply this."

Worked example

Situation

On one team, I had someone who was technically strong but consistently invisible outside our immediate group: good work, no one above our manager knew it.

Applying the right lever

Coaching wasn't the gap (their skills were fine); mentoring alone wouldn't fix visibility either. The actual lever was sponsorship: in a planning discussion where a cross-team project needed an owner, I explicitly proposed them by name, with a specific example of relevant work, rather than waiting for them to volunteer themselves or be noticed organically.

Result

They were staffed onto the project and, importantly, presented their own results directly to the wider group afterward, which is the mechanism by which sponsorship compounds: one door opened, and the visibility from walking through it created future opportunities without needing me to open every subsequent door.

Trade-offs & pitfalls

• Treating all four as interchangeable. Coaching someone who actually needs sponsorship, or the reverse, wastes time and can be frustrating for the person, since you're addressing the wrong constraint.
• Sponsorship without real work behind it. Advocating for someone who isn't actually ready burns your own credibility and sets the person up to struggle publicly; sponsorship should follow demonstrated capability, not replace it.
• Forgetting that management overlaps with the other three. A manager routinely coaches day to day, mentors for career conversations, and sponsors their strongest people; the four aren't mutually exclusive roles held by different people, though they often are in practice.

Direct answer

Beyond CUPED (using a pre-experiment covariate to residualize the outcome), the two other standard variance-reduction levers are stratified (blocked) randomization, which forces balance on a known factor at assignment time instead of hoping random chance balances it, and covariate or regression adjustment, which is the general case of "adjust for a predictive covariate" that CUPED is one specific, pre-experiment-only instance of. Both work by removing a source of outcome variance that is not related to treatment, so the same true effect becomes easier to distinguish from noise; both reduce required sample size roughly in proportion to how much outcome variance the factor explains, and neither invents a new number, they trade a known, explainable source of variance for a smaller residual.

Structured elaboration

Stratified (blocked) randomization

Instead of randomizing the whole population as one pool, split the population into strata on a factor known before assignment (country, device type, new vs. returning user), then randomize independently within each stratum so each arm gets a matched share of every stratum. This removes between-stratum variance from the treatment-effect estimator's variance, because the strata are balanced by design rather than by luck: with plain randomization on a highly imbalanced population, an unlucky split (e.g., treatment skewing toward the low-baseline country) inflates the observed variance of the effect estimate even though the true effect is unaffected.

It is applicable whenever you have a discrete, pre-assignment factor that is known to correlate with the outcome and is stable at randomization time. It differs from covariate adjustment in when the correction happens: stratification acts at assignment time (balance is enforced), while regression adjustment acts at analysis time (balance is estimated and subtracted after the fact). The two are complementary, not substitutes: stratify at assignment for the factors you can, and adjust for continuous covariates at analysis.

Covariate / regression adjustment

This is the general technique of fitting a model for the outcome on one or more covariates (not restricted to pre-experiment-only, unlike CUPED) and using the model to remove predictable variance from the outcome before comparing arms, most simply via ANCOVA (analysis of covariance), a linear regression of $Y$ on the treatment indicator and covariates that removes the variance those covariates explain from the comparison, the same variance-reduction logic as CUPED and stratification, just carried out as a regression rather than a pre-experiment covariate or a balanced split. It is applicable whenever you have covariates, pre-experiment or otherwise as long as they cannot themselves have been affected by treatment, that are predictive of the outcome. CUPED is the special case where the covariate is restricted to a pre-experiment value of the outcome metric itself; regression adjustment generalizes this to any number of eligible covariates and lets you combine several weak predictors into one stronger adjustment.

Effect on sample size and power

For both techniques, if the factor being controlled for explains a fraction $R^2$ of the outcome's variance, the variance of the treatment-effect estimator shrinks by roughly that same factor, and required sample size for a fixed target precision shrinks proportionally, since sample size for a fixed effect and power scales with the variance of the metric. A factor that explains little of the outcome variance buys little; a strong, well-chosen factor can meaningfully shorten the required test duration for the same statistical bar.

Worked example: stratifying a multi-country test

A test is planned across three countries with very different baseline conversion rates: Country A at 4%, Country B at 12%, Country C at 22%, in roughly equal traffic shares (each about one third of total users). Without stratification, plain randomization can by chance send more of one country's traffic to one arm, and even without that bad luck, the pooled outcome variance includes the between-country spread of baseline rates as extra noise the estimator has to average out.

Using the law of total variance, the overall variance of the outcome decomposes as:

$\operatorname{Var}(Y) = \underbrace{\mathbb{E}\big[\operatorname{Var}(Y \mid \text{country})\big]}_{\text{within-country variance}} + \underbrace{\operatorname{Var}\big(\mathbb{E}[Y \mid \text{country}]\big)}_{\text{between-country variance}}$

Stratifying by country and analyzing as a weighted average of within-country treatment effects removes the second (between-country) term from the treatment-effect estimator's variance, since each stratum is separately balanced and the between-stratum spread no longer contributes noise to the comparison. Concretely: with baseline rates of 4%, 12%, 22% and equal stratum weights, the between-country component of variance is

$\bar p = \frac{0.04+0.12+0.22}{3} = 0.1267$

$\operatorname{Var}(\bar p) = \frac{1}{3}\Big[(0.04-\bar p)^2 + (0.12-\bar p)^2 + (0.22-\bar p)^2\Big] = \frac{1}{3}(0.00751 + 0.0000445 + 0.00871) = 0.00542$

That 0.00542 is exactly the between-country variance component the stratified analysis removes from the pooled estimator's variance, computed directly from the three stated baseline rates, not asserted; how large a share of total variance that is depends additionally on the within-country binomial variance at each rate, which you would combine with this term using the same decomposition to get the full picture before quoting an overall percentage reduction.

Implementation for the multi-country case

• Assign the stratum at randomization time using the same deterministic hash-bucketing approach as the overall unit assignment, but nest it: hash within each country separately (or include country in the hash key) so each country independently hits its target split ratio.
• At analysis time, estimate the treatment effect within each country and combine as a weighted average (weighted by stratum size or by inverse variance), rather than pooling raw counts across countries, which is what actually realizes the variance reduction shown above.

Trade-offs and pitfalls

• Stratifying on too many dimensions at once shrinks individual strata until some contain too few units to balance meaningfully, and can create empty or near-empty cells, especially when crossing multiple categorical factors (country times device times cohort).
• A stratification factor chosen because it is convenient rather than because it is predictive buys little variance reduction while adding real implementation complexity; check the factor's explanatory power on historical data before committing the assignment pipeline to it.
• Regression adjustment on covariates measured close to, but not strictly after, the treatment start needs the same scrutiny as CUPED's pre-experiment-only requirement: any covariate that could plausibly be influenced by treatment invalidates the adjustment's unbiasedness, not just its efficiency.

Direct answer: An optimization fence is any construct that stops the query planner from seeing across a boundary to reorder joins, push a predicate down, or otherwise optimize the two sides together; pre-Postgres-12 common table expressions (CTEs, named WITH-clause subqueries) were the canonical example, always materialized and therefore always fenced. Separately, an ORDER BY written inside a CTE only guarantees the order of that CTE's own result as computed; nothing in the SQL standard requires the outer query to preserve that order once it selects from the CTE, so relying on it to make a downstream LIMIT deterministic is a latent bug that can pass code review and testing and then break silently on a planner upgrade, a different engine, or even just a different query shape elsewhere in the statement.

Structured elaboration

What "fence" means concretely: a fenced CTE is optimized as its own self-contained unit first, independently of the rest of the query, and only the finished result is handed up. A predicate in the outer query that would have let an index narrow down the CTE's own scan can't reach across the fence to do so; a join that would have been more efficient reordered around the CTE's contents can't be reordered, because the planner treats the fenced region as an opaque black box.

-- Pre-12 Postgres (or MATERIALIZED anywhere): expensive is computed in full before the outer filter runs
WITH expensive AS (
  SELECT * FROM large_table WHERE complicated_expr(...)
)
SELECT * FROM expensive WHERE small_col = 5;

-- Rewritten to remove the fence: an inline derived-table subquery was never fenced this way
SELECT * FROM (
  SELECT * FROM large_table WHERE complicated_expr(...)
) t
WHERE small_col = 5;

On Postgres 12+, the same effect is available without rewriting: add NOT MATERIALIZED to let the planner inline the CTE and push small_col = 5 down; add MATERIALIZED to force the fence back on intentionally (for instance, to guarantee single evaluation of an expensive or volatile CTE referenced more than once).

The ORDER BY-inside-a-CTE trap:

-- Risky: relies on the CTE's own ORDER BY surviving into the outer LIMIT
WITH ranked AS (
  SELECT id, score FROM scores ORDER BY score DESC
)
SELECT * FROM ranked LIMIT 3;

Nothing in the relational model guarantees this returns the top 3 by score. A CTE's result is logically a set (or, more precisely, an unordered rowset once you select from it in a further query), and an ORDER BY inside the CTE only defines the order of that CTE's own output, not a property the outer SELECT ... LIMIT is obligated to preserve. In practice this often "just works," which is exactly what makes it dangerous: it can pass every test you run against it on your current engine and version, then silently pick the wrong 3 rows after a planner change, an engine migration, or a query rewrite elsewhere in the same statement that changes the plan shape.

The correct fix, two ways:

-- Fix 1: repeat the ORDER BY at the level that actually applies the LIMIT
WITH ranked AS (
  SELECT id, score FROM scores
)
SELECT * FROM ranked ORDER BY score DESC LIMIT 3;

-- Fix 2: make the order an explicit data column via ROW_NUMBER, then filter on it
WITH ranked AS (
  SELECT id, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rn FROM scores
)
SELECT id, score FROM ranked WHERE rn <= 3 ORDER BY rn;

Fix 1 is the minimal correct version: put ORDER BY next to the LIMIT that depends on it, not one level removed inside the CTE. Fix 2 goes further by turning "the order" into an actual column (rn), which survives any amount of further querying because it's data now, not a side-channel property of how the result happened to be produced; this is the safer choice once the ranked result needs to be joined, filtered, or re-aggregated further before the final LIMIT is applied.

Worked example (executed in DuckDB)

Five rows with scores 50, 90, 70, 60, 80. All three versions (the risky pattern, fix 1, and fix 2) returned the identical top-3 result (scores 90, 80, 70) when run against this engine and this simple query shape. That's the honest and slightly uncomfortable part of demonstrating this trap: it is not something you can reliably force to fail on demand, because whether the risky pattern happens to work depends on planner internals that are neither part of the SQL standard nor something a worked example can guarantee reproduces a failure. The takeaway isn't "I proved it breaks here"; it's that fixes 1 and 2 are correct by the language's actual guarantees regardless of engine or version, while the risky pattern is correct only by accident, and accidents are exactly what regress silently after an upgrade.

Trade-offs & pitfalls

• Treat "it happened to return the right order in testing" as zero evidence that the risky pattern is safe; the standard's guarantees, not observed behavior on one engine and version, are what to design against.
• MATERIALIZED (forcing a fence) and NOT MATERIALIZED (removing one) are Postgres-12+-specific keywords; other engines need a rewrite (derived-table subquery, or an explicit temp table for a guaranteed-single-evaluation fence) to get the equivalent effect.
• Oracle exposes a similar override via undocumented /*+ MATERIALIZE */ and /*+ INLINE */ hints placed inside the factored subquery itself, not as top-level WITH-clause syntax the way Postgres's keywords are; don't assume the syntax transfers even when the concept does.
• The ORDER BY trap compounds with LIMIT specifically; an ORDER BY inside a CTE that's never followed by a LIMIT anywhere downstream is comparatively harmless, since the full result set eventually gets consumed regardless of order, but the moment a LIMIT enters the picture at any level, order becomes load-bearing and needs to be guaranteed at that level, not assumed from upstream.