Lyft Data Analyst Interview Preparation Guide (Entry Level)

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.

Layout (sheet names: Inputs, P&L, Calculations, Sensitivity, Charts)

• Inputs: clean, single source of assumptions (Base price, Base volume, COGS/unit, Fixed costs, Tax rate, Proposed price change + range, elasticity).
• P&L: structured rows (Revenue, COGS, Gross Profit, OpEx, EBIT, Taxes, Net Income) and columns for Base Case, Scenario (10% increase), and Variants.
• Calculations: intermediate steps (price per unit, adjusted volume, revenue by product, variable costs).
• Sensitivity: data table outputs for multiple price/elasticity combos.
• Charts: waterfall (gross profit bridge) and tornado or 2D heatmap.

Key assumptions (explicit, in Inputs):

• Base price = $P0; Base volume = Q0 (period/year)
• COGS per unit (variable) and total fixed costs
• Tax rate and any phasing/timing effects
• Price elasticity of demand (own-price elasticity, e.g., -0.8) with rationale (historical % volume change vs % price change or benchmark)
• Implementation timing (full-year vs partial-year)

Linking price change to volume (example formula):

• %ΔPrice = (P1 - P0)/P0 ; P1 = P0 * 1.10
• Use elasticity ε (negative): %ΔVolume = ε * %ΔPrice
• New volume Q1 = Q0 * (1 + %ΔVolume)
• Revenue = P1 * Q1
• Gross Profit = Revenue - (COGS/unit * Q1)
• Net Income = Gross Profit - Fixed OpEx - Taxes

Excel implementation tips:

• Keep Inputs as named cells (P0, Q0, EPS, COGS).
• Use direct formulas: =P0*(1+price_change) ; =Q0*(1+elasticity*price_change)
• Add data validation and comments for sources.

Sensitivity presentation (one recommended approach):

• Two-way data table (price change on rows, elasticity on columns) showing Net Income or %ΔNet Income; visualize as heatmap in Charts sheet. Complement with a tornado chart ranking drivers (price change, elasticity, COGS/unit, fixed costs) showing impact on Net Income for +/- plausible ranges. This makes risk and upside explicit to stakeholders.

Statistical power is the probability an experiment will detect a true effect of a given size (1 − β). It depends on significance level (α), sample size, baseline conversion (p0), and the minimum detectable effect (MDE). Higher power (commonly 80% or 90%) means lower chance of a false negative.

Shortening the evaluation timeframe reduces the number of observed users/events. With fewer samples, power drops unless the effect or baseline event rate is large. That increases the risk of missing real changes.

Concrete examples:

• Baseline p0 = 5% (0.05), MDE = +1 percentage point (absolute change to 6% → relative 20%). For α=0.05 and target power 80%, required n per group ≈ 27,000. If you cut the observation window in half (half traffic), you'll get ~13,500 per group → power falls to ~50–60%, so you likely won’t detect the effect.
• Baseline p0 = 20%, MDE = +1 pp (to 21% → 5% relative). Required n per group ≈ 16,000. Shortening the window similarly reduces power but less severely when baseline is higher because variance p(1−p) is lower relative to p.
• Larger MDE (e.g., +3 pp) dramatically reduces required n. With p0=5% and MDE=+3 pp, n per group might be ~3,000 — a shortened window may still provide adequate power.

Rules of thumb and actions:

• If you must shorten the window, increase traffic allocation, accept a larger MDE, or raise α (not recommended) to recover power.
• Run an a priori power/sample-size calculation using baseline rate and desired MDE; simulate if metric has time-dependent variance.
• Monitor cumulative power but avoid peeking without correction (use sequential methods).

Takeaway: shorter observation = fewer samples = lower power, with the impact larger when baseline rates are low or MDE is small. Plan sample size to match the desired MDE and realistic timeframe.

Direct answer

In the first 60 seconds on an unfamiliar schema, scan for primary and foreign keys to understand the entity graph, check table row counts to spot which tables are the "big" transactional ones versus small reference tables, and note any obviously suspicious column names (timestamps, status/type enums, anything that looks like a business identifier) before writing a single query.

Structured elaboration

1. Keys first: list every table's primary key and foreign keys. This alone tells you the shape of the entity graph (which tables are "hubs," which are junction tables, which are lookups) faster than reading column-by-column.
2. Row counts and table sizes: a quick SELECT COUNT(*) (or checking catalog statistics if counting is expensive) per table tells you which tables are the high-volume transactional core versus small, slowly-changing reference or lookup tables; the big tables are where performance and data-quality issues concentrate.
3. Timestamps: note every created_at/updated_at/*_date column, since these tell you whether a table is append-only, mutable, or soft-deletable, and are usually the first thing you'll filter by in any real query.
4. Immediate questions to note down: which columns are nullable and what NULL means there (unknown? not-applicable? not-yet-happened?); whether any column that looks like a natural key (email, order_number) is actually enforced unique; and whether any table name or column name hints at multiple business concepts crammed into one table (a red flag for hidden complexity).
5. Prioritization: start inspecting the tables with the most foreign-key connections to others (the "hub" tables) and the largest row counts first, since those are both the most likely to contain data-quality issues and the most central to understanding the domain.

Worked example

On an unfamiliar orders/customers/order_items/payments/refunds schema, the 60-second scan would surface: orders and order_items are almost certainly the largest tables (one row per transaction, and multiple rows per order respectively) and the central hub of the graph via foreign keys; customers and payments are the next tier; and refunds referencing orders (not order_items) would immediately raise the question "does a refund apply to a whole order or can it be partial per line item," which is exactly the kind of ambiguity worth resolving before writing any analysis that assumes one or the other.

Trade-offs and pitfalls

• The temptation under time pressure is to start writing queries immediately; without the 60-second key-and-cardinality scan first, it's easy to write a join that silently multiplies rows (joining orders to order_items when you actually wanted one row per order) and never notice, because the query still "runs" and returns plausible-looking numbers.
• Row counts matter as much as the relationships themselves: a schema that looks simple on paper can have a 200-million-row order_items table sitting behind an innocuous-looking foreign key, and knowing that upfront changes how carefully you'd write any exploratory query against it.
• The single highest-value question to resolve early is usually about the grain of the biggest table (what does one row actually represent), because every subsequent aggregation's correctness depends on getting that right first.

Direct answer
Before presenting a chart with an unusual point or high day-to-day variance, run both statistical checks (is this point/pattern outside normal variation, or attributable to a known one-off cause) and business checks (does a known event explain it) to decide whether to smooth it, annotate it in place, or leave it as-is, since silently smoothing away a real event, or silently ignoring a genuine anomaly, are both misleading choices.

Structured elaboration

1. Statistical check: compare the point (or the recent volatility) against the metric's historical variation (e.g. is it beyond 2-3 standard deviations from a rolling baseline, or does it stay within the normal range once weekday/seasonal effects are accounted for).
2. Business check: cross-reference known events (a release, a marketing campaign, an outage, a data-pipeline change) that could explain the point; a statistically unusual point with a known, real cause should generally be annotated, not smoothed away.
3. Decision: annotate (keep the point visible with an explanation) when there's a real, known cause worth communicating; smooth (show a moving average or similar) when the audience's task is understanding the underlying trend and a single noisy point would distract without being individually meaningful; remove only in the rare case the point is a confirmed data error, and even then, disclose that a point was removed and why, rather than silently deleting it.
4. Smoothing methods and the raw-vs-smoothed trade-off: a simple moving average is easy to explain but lags real changes and can average across low-density periods misleadingly; LOESS (locally weighted smoothing) adapts better to local structure but is less transparent to a non-technical audience; exponential smoothing weights recent points more heavily, responding faster to genuine shifts than a simple moving average. Whichever method is used, show BOTH the raw series (lightly) and the smoothed overlay, so the audience can still see the underlying noise the smoothing is averaging over, rather than only ever seeing the smoothed version.

Worked example
A revenue chart averaging about $1.2M per month, with a trailing-12-month band of roughly $1.0M-$1.4M, shows one unusually high month at $2.1M, about $700K, or roughly 75%, above the trailing average and clearly outside the normal band, well past what typical month-to-month noise would produce. Checking against the marketing calendar reveals a single $700K bulk order landed that month, accounting for essentially the entire spike; because it's a real, known event (not noise or an error), the point stays on the chart with an annotation ("one-time $700K bulk order, not representative of ongoing trend") rather than being smoothed away or silently left unexplained.

Trade-offs and pitfalls
Smoothing or removing a data point without disclosing that you did so, and why, is one of the more damaging integrity issues in dashboard design, since it can make a real, actionable signal (or a real problem) invisible to the audience who needed to see it.

Direct answer

The interviewer wants to see whether you treat pushback as a signal to investigate rather than an obstacle to argue past. A strong answer names the specific objection, what you did to address it (more evidence, a smaller reversible test, surfacing a hidden concern), and the actual outcome, including if the recommendation still wasn't adopted.

Structured elaboration

Use a simple frame to structure the story:

• Situation: what the recommendation was and who pushed back, and roughly what their stated objection was.
• Task: what needed to happen next given that pushback.
• Action: the concrete steps you took, for example diagnosing the real underlying concern, bringing a smaller or lower-risk test instead of re-presenting the same evidence louder, or looping in someone the stakeholder trusts.
• Result: what actually happened, plus what you'd do differently, even if the honest answer is that they still said no.

Worked example

Example (illustrative, adapt to your own experience): a category manager dismissed a recommendation to shift ad spend away from a channel, saying "that channel builds our brand, the model doesn't capture that." Instead of re-presenting the same chart, the analyst asked what evidence would actually change the manager's mind, proposed a two-week holdout in a single region as a low-risk test, and came back with a direct regional comparison. The manager agreed to a partial reallocation for one quarter rather than the full change.

If you haven't faced this in a professional analytics role, use a project or coursework example where someone disagreed with a data-based conclusion, and focus the story on how you diagnosed why they disagreed rather than on the size of the business outcome.

Trade-offs and pitfalls

Avoid a story where the stakeholder is a strawman who simply came around, interviewers discount that. Also avoid defaulting to "let's run a pilot" for every disagreement, sometimes speed matters more than certainty and the right move is a smaller concession, not a new experiment.

What the interviewer probes next

Expect a follow-up on what you'd do if the additional evidence still hadn't changed their mind, or a question turned around to ask about a time the stakeholder's pushback turned out to be right.

Approach — threshold selection

• Pick the score threshold that selects the top 5,000 users by predicted churn score (i.e., the 5,000 highest scores). Practically that means choosing the score quantile q = 5000 / N (N = current weekly eligible population). If your monitoring only reports metrics at fixed quantiles (e.g., precision@10%), map 5,000 to the nearest quantile (example below).

Estimate expected true positives

• If 5,000 corresponds to the top 10% (i.e., N = 50,000), you can use the provided precision@10% = 0.45.
• Expected true positives (TP) = precision * targets = 0.45 * 5,000 = 2,250 retained users.

Financials / ROI calculation

• Benefit per true positive = $120 NPV.
• Total benefit = TP * 120 = 2,250 * 120 = $270,000.
• Targeting cost = 5,000 * $5 = $25,000.
• Net value = 270,000 - 25,000 = $245,000.
• ROI = Net / Cost = 245,000 / 25,000 = 9.8 → 980% (or benefit/cost ratio = 270,000 / 25,000 = 10.8).
• Break-even check: minimum TP to break even = cost / 120 = 25,000 / 120 ≈ 208.3 → min precision = 208.3 / 5,000 ≈ 4.17%. The model’s precision@10% (45%) is well above break-even.

If 5,000 is not 10%:

• Compute quantile q = 5,000 / N. If q differs from 10%, interpolate precision using validation PR curve or compute precision at that q directly from holdout data. Use that precision in the same formula TP = precision * 5,000.

Model limitations and how they affect recommendations

• Precision@10% is an empirical estimate — it depends on the validation set; real-world precision can differ due to population shift or seasonality. Always validate on recent data.
• AUC=0.78 indicates good discriminative power but doesn’t tell you calibration — predicted probabilities may need recalibration for correct quantile cuts.
• Precision/recall are aggregate metrics: they don’t account for heterogeneous lift (some users are easier to retain). Consider uplift modelling (causal effect) because some targeted users might have stayed without an offer; standard churn prediction overestimates incremental impact.
• Operational constraints: offer capacity, overlap with other campaigns, and customer experience (over-targeting) affect realized ROI.
• Recommendation: run a small randomized pilot / holdout A/B test that targets the chosen top-5000 group and a control group to measure actual retention lift and realized NPV, monitor model drift, recalibrate thresholds periodically, and consider moving toward uplift models for more efficient spend.

Direct answer
I'll use Amazon's "Customer Obsession" as the example: in plain terms it means starting from the customer's actual experience and working backward to the decision, rather than starting from what's easiest or cheapest for the team and working forward to how it will land on the customer. In day-to-day work that shows up as a specific, repeatable habit: before finalizing a decision, explicitly write down what the customer will experience as a result, not just what the team will ship.

Structured elaboration

1. State the value in plain language first, in one or two sentences, before layering on any nuance. A stated value is only useful if you can restate it without jargon; if you can't, you probably don't understand it well enough to apply it.
2. Trace two or three concrete decisions the value would actually change, not just decisions it would be compatible with. The test is not "does this decision fit the value" (almost any reasonable decision can be described as fitting almost any value after the fact); the test is "would I have decided differently without this value in mind."
3. Be specific about the mechanism, not just the outcome. It's not enough to say "I'd focus on the customer"; describe the actual practice (writing the customer-facing consequence down explicitly, reviewing a metric that measures customer impact rather than only internal effort, asking a specific question in a design review) that operationalizes the value day to day.
4. Acknowledge the value has a cost or a trade-off, because a value with no real cost usually is not being taken seriously. A genuinely operative value changes what you'd otherwise have done, which means it sometimes means doing the harder or slower thing.
5. Connect it back to your own role specifically, since the same value plays out differently for different functions; the mechanism for a backend engineer, a designer, and an analyst are all different concrete practices in service of the same underlying value.

Worked example
Say you're building a dashboard intended to help a seller reduce order defects. A team NOT applying customer obsession as a working discipline might ship the dashboard once the underlying data pipeline is stable and the metrics are technically correct, treating "the data is right" as the finish line. Applying the value changes the finish line: before shipping, you'd sit with two or three actual sellers using an early version and ask what decision they're trying to make when they open it, which might surface that they need same-day defect data to catch a bad batch before it ships further, not a metric that's accurate but a day stale. The concrete decision that changes: you invest in a same-day data refresh even though it's more engineering effort than the weekly batch job you'd planned, because the customer's real decision-making need, not the easier technical path, is what determines what "done" means. The cost is real (more pipeline complexity, tighter SLAs to maintain) which is exactly why it's evidence the value is actually operative rather than decorative.

Trade-offs & pitfalls
The most common failure is reciting the value's definition fluently and then giving an example so generic it would apply to any company with any stated value ("I always think about the user"), which demonstrates you've read the careers page rather than that you understand the mechanism. A second pitfall is picking an example where the value cost nothing: if every example you give was also simply the obviously correct engineering or business call regardless of the stated value, you haven't actually shown the value did any independent work in your reasoning. A third is over-indexing on one company's specific phrasing so heavily that the answer would sound out of place at any other employer; the goal is to show you can genuinely reason from a stated principle to a concrete decision, a transferable skill, not that you've memorized one company's vocabulary.

Direct answer

Investigate a near-constant column (95% one value, 5% something else) by first checking whether the rare non-null values are genuinely informative or just noise, and whether the column's near-constant nature is a real property of the world or an artifact of how the data was collected. If the rare values correlate meaningfully with anything else you care about, the column is worth keeping, likely as a flag ("is this the common value or not") rather than as a raw feature; if the rare values look like noise or errors, the column may be safe to drop.

What would change the answer

Check whether the 5% of non-default values cluster around anything specific (a particular time period, a particular customer segment, a particular data source), which would suggest they're a real, meaningful minority case rather than noise. Also check whether the dominant value looks like a genuine business state (say, "active" status for most rows) versus a suspicious default that might indicate a field nobody actually fills in reliably (like an optional form field left at its pre-filled default 95% of the time). The two scenarios call for very different conclusions even though the raw statistics look identical.

Worked example

A subscription_tier column is "free" for 95% of rows and something else for the remaining 5%. Checking whether that 5% correlates with anything: it turns out those users have dramatically higher engagement and retention, meaning the column, despite being heavily skewed, is carrying real, valuable signal about a small, genuinely different population. Contrast that with a referral_source field that's the literal string "unknown" for 95% of rows and a mix of real values for the rest: checking whether "unknown" correlates with a specific signup date range reveals it was simply never collected before a certain date, meaning the near-constant value here is a data-collection artifact, not a meaningful state.

Trade-offs and pitfalls

Don't drop a near-constant column reflexively just because it looks like "not much information": a rare-but-meaningful minority value can be one of the most useful signals in the whole dataset, precisely because it's rare and distinguishes a small group from everyone else.

Direct answer

Contrast, before-and-after, and the 'so what' chain are three different ways to make a number land; contrast works by comparison, before-and-after works by showing change over the SAME thing, and the 'so what' chain works by repeatedly answering "and why does that matter" until you reach a decision.

Structured elaboration

• Contrast: place the finding next to something the audience already has intuition for. For a conversion decline, contrast it against a competitor benchmark or against the company's own historical best, so the number has a reference point rather than floating in isolation.
• Before-and-after: show the SAME metric at two points in time, ideally visually side by side, so the change itself is the story rather than either snapshot alone.
• 'So what' chain: state the fact, then ask "so what does that mean" and answer it, then ask "so what does THAT mean" again, continuing until you reach something the audience can act on, rather than stopping at the first, still-abstract answer.

Worked example

For a conversion rate decline from 5% to 3% over six months:

• Contrast: "Our 3% conversion rate is now below the 4% industry benchmark for our category, when six months ago we were above it."
• Before-and-after: a simple two-bar chart, "5% six months ago" next to "3% today," with the six-month trend line in between showing it wasn't a single cliff but a steady erosion.
• So-what chain: "Conversion fell from 5% to 3%. So what? At our current traffic, that's about 2,000 fewer customers a month. So what? At our average order value, that's roughly $180,000 in monthly revenue. So what? That's larger than the cost of the fix we're proposing, which is why I think this is worth prioritizing this sprint."

Trade-offs and pitfalls

Each technique fails if pushed past its natural stopping point: a contrast against a benchmark that isn't truly comparable (different industry, different customer base) misleads more than it clarifies; a before-and-after chart with too many intermediate points buries the headline change in noise; and a so-what chain that keeps going past the point of actionability starts to feel like a lecture rather than a build-up to a decision. Stop the chain exactly at the sentence that makes the case for action, and no further.