Lyft Senior Data Analyst Interview Preparation Guide

Direct answer: Treat "different results on two machines" as an environment-parity problem first and a pandas-logic problem second: confirm the pandas/numpy/dependency versions actually match, then add explicit dtype casts, an explicit sort key, and a tolerance-based comparison to the transformation itself so any remaining difference is forced to show up as a concrete, inspectable diff rather than a vague "the numbers are different."

Structured elaboration: debugging plan

1. Confirm environment parity (Python, pandas, numpy, and their native dependencies like BLAS/LAPACK).
2. Reproduce the failure on a small, concrete input you can run in both places.
3. Make the transformation deterministic: explicit dtypes, explicit sort key, stable sort algorithm.
4. Compare outputs with a tolerance-aware assertion instead of eyeballing numbers.
5. Once you have a reproducible diff, narrow the cause: dtype coercion, unstable sort/groupby ordering, or genuine floating-point difference from a different math backend.

1. Environment parity

import sys, platform
import pandas as pd, numpy as np

print("python:", sys.version.splitlines()[0])
print("platform:", platform.platform())
print("pandas:", pd.__version__)
print("numpy:", np.__version__)
pd.show_versions()  # prints pandas + every dependency's version; don't wrap it in print(), it already prints and returns None

Run pip freeze (or conda list) on both machines and diff the two outputs directly; a mismatched numpy, pyarrow, or BLAS library is the single most common cause of "same code, different numbers."

2-4. A concrete, reproducible transformation with built-in invariants (verified, pandas 3.0.3):

from pandas.testing import assert_frame_equal

def normalize_df(df):
    df = df.copy()
    df = df.reset_index(drop=True)
    if 'id' in df: df['id'] = df['id'].astype('int64')
    if 'cat' in df: df['cat'] = df['cat'].astype('category')
    sort_keys = ['id'] if 'id' in df.columns else list(df.columns)
    df = df.sort_values(sort_keys, kind='mergesort')  # stable sort: ties keep input order
    df = df.reset_index(drop=True)
    return df

def transform(df):
    df2 = df.assign(value=df['a'] / df['b'])
    grouped = df2.groupby(['cat', 'id'], sort=True, as_index=False).agg({'value': 'mean'})
    return grouped

a = pd.DataFrame({
    'id':  [3, 1, 2, 1],
    'cat': ['x', 'y', 'x', 'y'],
    'a':   [10.0, 20.0, 30.0, 5.0],
    'b':   [2.0, 4.0, 3.0, 2.0],
})

left = normalize_df(transform(a))   # stand-in for "developer laptop output"
right = normalize_df(transform(a))  # stand-in for "production output"

try:
    assert_frame_equal(left, right, check_dtype=True, check_exact=False, rtol=1e-9, atol=1e-12)
    print("Frames equal within tolerance")
except AssertionError as e:
    print("Assertion failed:", e)
    diff = left.compare(right, align_axis=0)
    print(diff.head(50))

Verified output on this fixture: Frames equal within tolerance, and left is

  cat  id  value
0   y   1   3.75
1   x   2  10.00
2   x   3   5.00

In a real "diverges between machines" scenario, left would be the developer laptop's saved output and right the production output loaded from wherever it was captured; the point of normalize_df is to remove sources of incidental difference (row order, index values, dtype) before you ask whether the values differ.

Checks and invariants worth adding

• df.dtypes.value_counts() and, for categoricals, df['cat'].cat.categories, a dtype or category-set mismatch is the most common silent cause.
• df.isna().sum() and np.isfinite(df.select_dtypes(float)).all() to catch unexpected nulls or infinities (division by zero, overflow) before they propagate into an aggregation.
• Force a stable sort (kind='mergesort') before any groupby whose downstream code assumes row order, the default sort is not guaranteed stable across all inputs.
• If you use Parquet/pyarrow anywhere in the pipeline, confirm pyarrow.__version__ parity too and pin engine='pyarrow' explicitly rather than letting pandas auto-select an engine that may differ by environment.
• Add an automated CI check that fails the build when pip freeze on the CI runner diverges from a locked requirements/lockfile, catching the root cause before it ever reaches production.

Complexity: the checks above are dominated by the sort (O(n log n)) and the groupby (O(n) average with a hash-based grouping), the same order of work the original transform already does, so adding them does not change the pipeline's asymptotic cost. The assert_frame_equal comparison is O(n * m) in the size of the two frames being compared.

Edge cases

• Empty input on one machine: assert_frame_equal on two empty frames with matching dtypes passes trivially; verify dtypes still match on an empty frame; an empty groupby result can silently have a different dtype (e.g. object instead of float64) than a non-empty one.
• All-NaN column: df['a'] / df['b'] where b is 0 produces inf, not NaN (not-a-number, pandas' missing-value marker), catch it explicitly with the isfinite check above rather than assuming a null check is sufficient.
• Duplicate index or duplicate group keys: reset_index(drop=True) before comparing sidesteps index-value mismatches entirely, always drop the index in the comparison unless the index itself is part of what you're validating.
• Timezone-aware datetimes: a tz-naive timestamp on one machine and a tz-aware one on the other compare as different dtypes entirely (assert_frame_equal will fail on dtype, not just value), normalize both to a single, explicit timezone before comparing.

Join the parent aggregate to the sum of its children and flag any pair whose difference exceeds a small floating-point tolerance, treating a strict equality check as almost always the wrong choice for numeric data that's passed through any arithmetic at all.

Approach (verified by execution)

```sql
SELECT o.order_id, o.total_amount, SUM(oi.amount) AS items_sum,
ABS(o.total_amount - SUM(oi.amount)) AS diff
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.total_amount
HAVING ABS(o.total_amount - SUM(oi.amount)) > 0.01;
```

Worked example (verified by execution)

An order recorded with `total_amount = 55.00` whose line items sum to `49.99` produces a diff of `5.01`, well past the 0.01 tolerance, correctly surfacing as a violation; a second order whose total matches its line items exactly does not appear in the result at all.

Trade-offs and pitfalls

The same invariant-checking shape (a recorded total versus a computed sum of its parts) generalizes to checking that a day's hourly metric values sum to the recorded daily total, and to checking that a dimension's full set of expected values still appears in a daily aggregate even on a day with zero activity for one of them (so a category doesn't silently vanish from a report rather than showing a legitimate zero). A related but distinct check worth running alongside this one is completeness of PAYMENT against an order's expected total, which needs its own comparison (partial or missing payments) rather than being folded into the same query, since "the order total doesn't match its line items" and "the order hasn't been fully paid" are different failures with different remediation paths even though both compare a recorded amount to an expected one.

Situation: I needed the Support team to invest time in a new escalation process, but I had no formal authority over them.

Task: My job was to earn their support instead of asking for compliance.

Action: I started by learning what mattered to them. I met with the support manager and a few frontline agents to understand their pain points. The main issue was that they were already overloaded, so any new process had to save time, not add work. I then reframed my initiative in their language. Instead of talking about my project milestones, I showed how the change would reduce repeat tickets and make it easier to resolve customer issues on the first contact. I also offered a small pilot so they could test the idea before committing fully.

Result: Because I tied the request to their goals and reduced the burden, they agreed to participate and became active partners in the rollout.

What I learned is that influence comes from understanding the other function’s priorities, showing respect for their constraints, and making the benefit concrete.

Direct answer. RFM scoring, k-means clustering, and rule-based personas sit on a spectrum from simple and fully interpretable to flexible and data-driven but harder to explain. RFM scoring ranks and buckets users on recency, frequency, and monetary value using fixed rules; k-means clustering discovers groups statistically from a broader set of behavioral features; rule-based personas are hand-authored groupings a product team defines directly from domain knowledge, without necessarily running any algorithm at all.

Structured elaboration.

Worked example. For an e-commerce dataset, RFM is a strong default when the goal is a lifecycle-marketing action (which segment gets a win-back email, which gets a loyalty offer) because the resulting tiers map directly to a playbook everyone already understands. K-means clustering on a richer feature set (category affinity, price sensitivity, session patterns) is a better fit when the goal is discovery, for example finding an unexpected behavioral group worth a dedicated product investment, where you do not already know what the useful cut looks like. Rule-based personas are the right choice when speed and stakeholder buy-in matter more than statistical rigor, for example a first pass before there is enough data or engineering time to run a clustering pipeline.

Trade-offs and pitfalls. The most common mistake is choosing k-means because it sounds more rigorous, then failing to invest in the ongoing work of labeling, validating, and re-fitting clusters, so the "sophisticated" segmentation quietly goes stale while a simpler RFM or rule-based scheme would have kept working with far less upkeep. The reverse mistake is sticking with a hand-written persona long after usage patterns have shifted underneath it, because nothing in a rule-based scheme forces anyone to notice the drift.

Direct answer

A pivot table (or a GROUP BY aggregation in SQL) reorganizes raw, row-level data into a summary where one categorical variable becomes the rows, another becomes the columns, and a numeric measure gets aggregated (summed, averaged, counted) into each cell. It turns "here are a million individual transaction rows" into "here's total sales by region and month," which is a shape a human can actually scan for a pattern.

Why this matters for EDA

Raw rows almost never reveal a pattern directly, since any one transaction is just one data point among many; the pattern lives in the aggregate, and a pivot is the fastest way to get from rows to a scannable aggregate without writing a full report. Because it's cheap to reshape (swap what's on the rows versus the columns, or add a second grouping variable), a pivot is also a fast way to explore several candidate breakdowns of the same data before committing to one.

Worked example

Pivoting a transactions table with sales by region (rows) and month (columns), summing the amount column into each cell, quickly reveals that one region's sales spike sharply in a single month while every other region stays flat. That pattern, invisible in the million raw rows, is immediately visible once it's aggregated into a 5-region-by-12-month grid you can scan in seconds. Following up on just that one cell (what happened in that region, that month) is a much more targeted investigation than starting from the raw transaction log.

Trade-offs and pitfalls

A pivot hides the underlying spread: two cells can show the same average while one is built from consistent values and the other from a few extreme ones cancelling out. Treat a pivot as a way to spot WHERE to look closer, not as the final word on what's happening in a cell that looks interesting.

Direct answer

Quantify the risk of excluding a small or under-sampled segment by showing, side by side, how wide that segment's confidence interval actually is compared to a well-sampled segment with the same point estimate, and what decisions could plausibly flip within that range. Pair the direct-estimate uncertainty with a model-based (small-area) estimate that borrows strength from a broader population, and translate both into a concrete business-impact number rather than leaving the argument as an abstract statistical one.

Structured elaboration

Quantifying the uncertainty. For a segment with sample size $n$ and observed rate $\hat p$, the margin of error at 95% confidence is:

Margin of error scales with $1/\sqrt n$, so a segment with 100x fewer observations than a comparison segment has a margin of error 10x as wide at the same point estimate - which is often the single clearest way to make the risk concrete to a non-technical stakeholder.

Small-area / model-based estimation. When $n$ is too small for a trustworthy direct estimate, a Fay-Herriot-style hierarchical (shrinkage) estimator combines the noisy direct estimate with a more stable prior (e.g. the regional or overall average) weighted by their relative precision:

where $\psi$ denotes the variance of each input. This gives a lower-variance point estimate for very small segments at the cost of pulling them toward the broader population, which should be reported alongside the direct estimate, not as a silent replacement for it.

Sample-size and threshold rules. Set explicit, documented thresholds: e.g. direct estimates trusted above some minimum $n$, model-based (shrunk) estimates used and clearly labeled between a lower and that minimum, and segments below a hard floor flagged as "insufficient to report a point estimate" rather than silently either excluded or shown with false precision.

Communicating the business risk. Translate statistical uncertainty into a decision-relevant number: given the segment's size and margin of error, what's the range of plausible business impact (revenue, users affected) if the segment is excluded versus included with appropriate caveats? Visuals that work well: a forest plot of segment estimates with CIs side by side, and a simple scenario table showing "if we exclude this segment and it's actually at the low end of its plausible range, we miss X; if we include it and it's at the high end, we overreact by Y."

Worked example

A small geographic segment ($n=45$) versus a well-sampled comparison segment ($n=4{,}500$) with the same observed rate:

import numpy as np
from scipy import stats
z95 = stats.norm.ppf(0.975)

n_small, p_hat = 45, 0.30
moe_small = z95 * np.sqrt(p_hat*(1-p_hat)/n_small)

n_large = 4500
moe_large = z95 * np.sqrt(p_hat*(1-p_hat)/n_large)

Output:

small segment: n=45,   p_hat=30%, MOE(95%)=+/-13.4pp, CI=[16.6%, 43.4%]
large segment: n=4500, p_hat=30%, MOE(95%)=+/-1.34pp, CI=[28.7%, 31.3%]
MOE ratio (small/large) = 10.00x = sqrt(n_large/n_small)

A 45-user segment's "30%" is genuinely consistent with anything from about 17% to 43% - a range wide enough to span two very different business stories. Applying Fay-Herriot shrinkage with a regional prior mean of 45% (assumed variance 0.002) instead of dropping the segment:

direct_var, regional_var, regional_mean = moe_small**2/z95**2, 0.002, 0.45
weight_on_direct = regional_var / (regional_var + direct_var)
shrunk_estimate = weight_on_direct*p_hat + (1-weight_on_direct)*regional_mean
shrunk_se = np.sqrt(1/(1/direct_var + 1/regional_var))

Output:

weight on the direct (small-segment) estimate = 0.30
shrunk estimate = 40.5%, SE = 3.74pp   (vs direct SE 6.83pp)

The shrunk estimate is both more stable (SE nearly halved) and pulled toward the regional pattern, giving stakeholders a defensible number to act on instead of either a wildly uncertain direct estimate or an outright exclusion that erases the segment from the conversation entirely.

Trade-offs & pitfalls

Silently dropping small segments from reporting is the most common and most damaging failure: it doesn't remove the uncertainty, it just hides it, and typically biases aggregate numbers toward whatever the well-sampled majority looks like. The opposite mistake, reporting a small segment's direct estimate with the same visual confidence as a well-sampled one (no CI, no sample-size caveat), invites stakeholders to over-react to noise. Shrinkage estimators solve the precision problem but introduce their own risk: if the small segment is genuinely different from the broader population (not just noisy), shrinkage will systematically bias it toward a prior that doesn't actually describe it, so the prior's appropriateness (how similar is this segment really expected to be to the pooled group) should be argued explicitly, not assumed by default.

Summing total historical lifetime revenue per customer is flawed because it mixes cohorts with different behaviours, ignores censoring (customers still active), and treats future value as equal to past dollars. That biases decisions and overstates LTV.

A more robust method:

1. Use cohorts and cohort-weighted averages — group customers by acquisition period (week/month). Compute average revenue per user (ARPU) by cohort and by age (month 0,1,2...). Why it matters: isolates acquisition-channel or time effects so you don’t conflate old high-value cohorts with new ones; enables fair comparison and trend detection.

2. Build retention (survival) curves and per-period revenue curves — for each cohort, compute retention rate r_t and average revenue per retained user v_t at each period t. Estimate expected future revenue as sum over future periods: LTV_cohort = Σ_{t=0..T} r_t * v_t. Why: captures when customers churn and how revenue decays over time, producing realistic forward-looking estimates.

3. Discount future cash flows — apply a discount rate d to convert future revenue to present value: PV_LTV = Σ r_t * v_t / (1 + d)^t. Why: accounts for time value of money and opportunity cost; important for comparing CAC and payback.

Additional improvements: account for censoring via survival analysis or parametric fits (exponential/Weibull) to extrapolate beyond observed window; segment by product/behavior to avoid averaging heterogeneous groups; report confidence intervals and sensitivity to discount rate.

Outcome: cohort + retention + discounting yields comparable, unbiased, and decision-ready LTV estimates for CAC budgeting and product strategy.

Direct answer

You measure whether your communication actually works the same way you'd measure any other process: define what 'acted on' looks like concretely, instrument it, and track it over time, rather than assuming a well-received presentation equals a changed decision.

Structured elaboration

1. Separate 'insight was delivered' from 'insight was acted on.'
Most teams only track the former (a deck was presented, a dashboard exists) because it's easy to observe. The real signal is whether a decision, a roadmap item, or a resourcing choice actually changed as a result. That requires deliberately logging each insight or recommendation as a discrete, trackable unit (a ticket, a decision-log entry, a recommendation ID) rather than letting it live only inside a slide deck that nobody revisits.

2. Define 4-6 concrete metrics that make actionability observable.
A reasonable, non-exhaustive set: (a) share of recommendations formally accepted, rejected, or deferred within a defined window (e.g. 30 days) - the acceptance rate; (b) average time from delivery to a decision being made on it - time-to-decision; (c) share of accepted recommendations that were actually implemented, not just approved - the follow-through rate, since approval without implementation is a common failure mode; (d) measured downstream business impact where an accepted recommendation included a predicted effect (did the metric move the way the insight predicted, and by how much); (e) a stakeholder-reported usefulness or trust score, gathered periodically, as a leading indicator; and (f) recurrence rate of the same insight being re-delivered because it was previously ignored, which is a strong negative signal.

3. Build the minimal data collection to make this trackable, not a large new system.
In practice this is a lightweight log: each insight gets an ID, a delivery date, an owner, a decision outcome, and (if applicable) a link to the metric it was supposed to move. This can live in an existing ticketing or decision-log tool rather than requiring new infrastructure; the discipline is in the LOGGING HABIT, not the tooling.

4. Assign ownership and a reporting cadence.
The team that produces insights (analytics, data science, BI) should own tracking whether insights were delivered and understood; the business owner who received the recommendation should own logging the decision outcome, since they are the one who knows whether it was actually acted on. Report the rollup on a cadence that matches how often recommendations are made (commonly monthly or quarterly) rather than in real time, since 'time to decision' for a nontrivial recommendation is naturally measured in weeks, not hours.

Worked example

A data science team delivers 40 recommendations over a quarter (for example: adjust a pricing tier, change an onboarding step, retire an underperforming feature). They log each with an ID and owner. At quarter end: 28 of 40 were formally decided within 30 days (70% decision rate), of which 19 were accepted, 6 rejected, and 3 deferred; of the 19 accepted, 14 were actually implemented within the quarter (a 74% follow-through rate on acceptances); and of those 14, 9 had a predicted metric attached, of which 6 moved in the predicted direction by at least half the predicted magnitude. The team also finds that 5 of the 40 recommendations were substantively the same insight delivered a second time because the first delivery was never decided on, a recurrence signal that prompts them to investigate why certain recommendation types stall (in this case, three of the five involved a cross-team dependency with no clear single decision-owner). That specific finding, a missing decision-owner for cross-team recommendations, becomes the actionable process fix, which is itself an example of the framework working as intended.

Trade-offs and pitfalls

• The biggest pitfall is conflating 'stakeholders liked the presentation' with 'a decision changed'; a positive reaction in the room is not evidence of actionability and should not substitute for the follow-through metrics above.
• Attributing a downstream metric move entirely to one recommendation is often overclaiming, since other changes happen concurrently; where possible, treat the predicted-impact check as a directional signal, not a rigorous causal claim, and say so.
• A high recurrence rate is more informative than a low acceptance rate; recommendations legitimately get rejected for good reasons, but a recommendation that keeps resurfacing because no one ever decided on it points to a process gap, not a communication gap.
• Do not build a heavy new tracking system before establishing the logging habit manually; teams that try to automate this before anyone consistently logs decisions end up with clean-looking dashboards over incomplete data.

Direct answer. Window functions show up in a plan as a distinct operator that typically requires the input already sorted (or explicitly sorts it) by the function's PARTITION BY and ORDER BY columns; the most common performance problem is exactly that implicit sort, especially when it's applied over a much larger set of rows than the final result actually needs.

Structured elaboration. A window function computes its result per row while having visibility into a defined "window" of related rows (a partition, optionally further restricted by frame bounds), which the engine typically implements by first ensuring the input is ordered appropriately, then doing a single pass computing the running or ranked value per row. If there's no supporting index providing that order already, the plan pays for an explicit sort before the window computation runs, and if the query computes several DIFFERENT window functions with different PARTITION BY or ORDER BY clauses, the engine may need multiple separate sort passes, one per distinct windowing specification, rather than one shared pass.

Two realistic mitigation directions: an index matching the most commonly-used PARTITION BY plus ORDER BY combination can eliminate that sort the same way an index eliminates a plain ORDER BY sort; and, where the query only actually needs the window function's result over a much smaller slice than the full table (a "last 30 days" ranking, say), applying that filter BEFORE the window computation, rather than computing the window over the whole table and filtering the ranked result afterward, shrinks the input the expensive sort-and-window pass has to handle in the first place.

Worked example. A dashboard ranking "top products this week" using ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) computed over the ENTIRE sales history, then filtered down to this week's rows afterward, pays the full sort-and-rank cost over every historical row before discarding almost all of it; restructuring the query to filter to this week's rows FIRST, in a subquery or CTE, before applying the window function, computes the same ranking over a dramatically smaller input.

Trade-offs and pitfalls. Be careful that filtering before a window function doesn't accidentally change its semantics: a running total or a rank that's SUPPOSED to reflect the full historical context (not just the filtered slice) would be silently wrong if you filter first; only push the filter earlier when the window function's intended meaning is genuinely scoped to that filtered slice, not the full unfiltered history.

Approach
I would define sessions per canonical user ID, not per device, because the requirement says the same user can act from multiple devices. First dedupe duplicate events, then sort by event timestamp, and start a new session when the gap from the previous event is more than 30 minutes.

WITH deduped AS (
  SELECT *
  FROM (
    SELECT
      e.*,
      ROW_NUMBER() OVER (
        PARTITION BY event_id
        ORDER BY ingest_ts DESC
      ) AS rn
    FROM clickstream e
  ) x
  WHERE rn = 1
), ordered AS (
  SELECT
    user_id,
    event_ts,
    CASE
      WHEN LAG(event_ts) OVER (
        PARTITION BY user_id
        ORDER BY event_ts
      ) IS NULL
      THEN 1
      WHEN event_ts - LAG(event_ts) OVER (
        PARTITION BY user_id
        ORDER BY event_ts
      ) > INTERVAL '30 minutes'
      THEN 1
      ELSE 0
    END AS new_session
  FROM deduped
)
SELECT
  user_id,
  event_ts,
  SUM(new_session) OVER (
    PARTITION BY user_id
    ORDER BY event_ts
  ) AS session_id
FROM ordered;

Session metrics
Group by user_id and session_id to get event count, start time, end time, and duration.

Guardrails
Out-of-order events are handled by sorting on event_ts. Duplicates are removed first so they do not create fake activity or split a session.