Lyft Senior Data Analyst Interview Preparation Guide

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.

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.

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.

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.

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
Use a grouped (clustered) bar chart when you need to compare individual category values precisely across a few time periods or groups; use a stacked bar when the TOTAL and the composition both matter; and use a 100%-stacked bar specifically when only the relative MIX (not the absolute total) matters, since 100%-stacking deliberately discards the total.

Structured elaboration

• Grouped/clustered bars: place each category's bars side by side per period; best for a business question like "how did revenue for each of our 3 product lines compare quarter to quarter", where the audience needs to read off each product line's precise value in each quarter, not just the combined total; readability degrades past roughly 3-4 categories per group, since the bars become thin and hard to compare.
• Stacked bars: stack categories within one bar per period, showing both the total (bar height) and each category's absolute contribution; best for a business question like "how has total support ticket volume changed, and how much of that total comes from each priority level", where both the overall trend and the composition matter together. Comparing a MIDDLE segment's size across bars is genuinely hard, since middle segments don't share a common baseline.
• 100%-stacked bars: normalize every bar to the same height, showing only the proportional mix; best for a business question purely about composition shift over time (e.g. "is our channel mix shifting toward paid?"), but actively hides whether the total itself grew or shrank, which can mislead if the audience assumes the totals are also comparable.
• Readability with many categories/groups: past a handful of categories, any of the three becomes cluttered; consider limiting to the top categories plus "other," or switching to small multiples (one chart per category, all with a shared axis) instead.

Worked example
A channel-mix analysis: a 100%-stacked bar clearly shows that paid channels grew from 20% to 35% of total revenue over four quarters, but without a companion note or a paired stacked (non-normalized) bar, a viewer might wrongly assume total revenue grew too, when in fact total revenue was flat and only the MIX shifted.

Trade-offs and pitfalls
Always pair a 100%-stacked bar with the absolute total displayed somewhere (a companion KPI or a second small chart), since presenting mix-shift alone risks the audience conflating a proportional change with an absolute one.

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.

Direct answer

Convert a sample-size target into a run duration in two steps: compute the required sample size per variant with the standard two-proportion test formula, then divide that by how much daily traffic actually lands in each variant (daily uniques times allocation), rounding up. The formula gives you a headcount; the traffic split is what turns a headcount into a calendar.

Structured elaboration

Step 1: the sample-size formula

For a two-sided test comparing baseline conversion $p_1$ against a target conversion $p_2$, at significance $\alpha$ and power $1-\beta$:

$n = \frac{\left(z_{1-\alpha/2}\sqrt{2\bar p(1-\bar p)} + z_{1-\beta}\sqrt{p_1(1-p_1)+p_2(1-p_2)}\right)^2}{(p_2-p_1)^2}, \quad \bar p = \frac{p_1+p_2}{2}$

This is per-variant sample size; assumes a two-sided test with no sequential peeking (one look at the end) and independent, one-conversion-per-user data. Sequential monitoring and multiple-comparisons corrections change this number and are a separate design decision, not part of the raw duration estimate.

Step 2: convert daily traffic into per-variant daily volume

$\text{daily per variant} = \text{daily unique visitors} \times \text{allocation share}$

Step 3: convert sample size into days

$\text{days} = \frac{n}{\text{daily per variant}}$

Round the sample size up (never down, since undershooting trades away the power you asked for) and round the resulting day count up to a whole day. If the metric has meaningful weekday/weekend variation, round further up to a whole number of weeks so every arm sees the same mix of weekdays and weekends; that decision is a separate seasonality question with its own reasoning (see the seasonality-planning answer), not something the raw formula above accounts for.

Assumptions and rounding choices worth stating out loud

• Two-sided test with a single, pre-planned final look; no interim peeking.
• Users are independent and contribute one conversion event each; no repeated exposure double counting.
• All intermediate values kept to about five significant figures before rounding the final answer, and the final day count always rounds up, not to the nearest day.
• A buffer of roughly 10-20% is a common practical addition on top of the raw day count to absorb data loss (bot filtering, QA holds, instrumentation gaps); state it as a buffer, not as part of the statistical requirement.
• If daily traffic is volatile rather than a single stable number, use a conservative (lower) daily estimate for the duration calculation rather than the average, since under-running the test is far more costly than over-running it by a day or two.

Worked example

Inputs: 60,000 daily unique eligible visitors, 50/50 allocation, baseline conversion $p_1 = 3.0\%$, desired minimum detectable effect of 8% relative, $\alpha=0.05$ two-sided ($z_{1-\alpha/2}=1.9600$), power 80% ($z_{1-\beta}=0.8416$).

Absolute MDE: $\Delta = 0.03 \times 0.08 = 0.0024$, so $p_2 = 0.0324$, $\bar p = 0.0312$.

$z_{1-\alpha/2}\sqrt{2\bar p(1-\bar p)} = 1.9600 \times \sqrt{2 \times 0.0312 \times 0.9688} = 1.9600 \times 0.2459 = 0.4819$

$z_{1-\beta}\sqrt{p_1(1-p_1)+p_2(1-p_2)} = 0.8416 \times \sqrt{0.03\times0.97 + 0.0324\times0.9676} = 0.8416 \times 0.2459 = 0.2069$

$n = \frac{(0.4819+0.2069)^2}{(0.0024)^2} = \frac{0.4744}{0.00000576} \approx 82{,}376 \text{ users per variant}$

Daily per variant: $60{,}000 \times 0.5 = 30{,}000$.

$\text{days} = \frac{82{,}376}{30{,}000} \approx 2.75 \rightarrow \text{round up to } 3 \text{ days minimum}$

Because this crosses a weekday/weekend boundary either way, the practical duration recommendation would be at least 7 days (one full week) rather than the bare 3-day statistical minimum, so weekday and weekend behavior are represented in the same proportion in both arms.

Trade-offs & pitfalls

• MDE sensitivity. The required sample size scales with the inverse square of the MDE, so halving the effect you want to detect roughly quadruples the required sample and the resulting duration; a stakeholder asking for a smaller MDE "just to be safe" is asking for a much longer test, not a marginally longer one.
• Binary-outcome formula does not transfer to continuous metrics. Revenue-per-user or time-on-task outcomes use the outcome's variance, not $p(1-p)$, in the same general formula shape; plugging a conversion-rate formula into a continuous metric silently understates or overstates the required sample.
• Shortcutting the calendar rounding. A raw day count under 7 does not mean the test is safe to run for that literal number of days; the weekly-cycle rounding matters as much as the raw arithmetic and is a common place teams cut a real corner under launch pressure.
• Traffic volatility. A single "daily uniques" number hides day-to-day swings; a duration plan built on a lucky high-traffic day will run short in practice.

Direct answer

Without formal authority, influence has to come entirely from credibility and voluntary buy-in instead of any ability to assign work or shape a review. That changes the approach toward explicit opt-in, keeping every session clearly worth their time, and respecting that they can walk away at any point without consequence.

What actually changes

• No mandate over cadence or topics. You can't schedule a recurring 1:1 and assume it happens, each session has to earn its place on their calendar.
• No visibility into their formal goals. You're advising without the context a manager has, so advice has to stay conditional ("here's what I'd consider, given what I know") rather than directive.
• No enforcement of follow-through. They can take or leave anything you suggest with no consequence, which is a feature, not a problem, but it means you can't measure success the way you would with a direct report.
• A boundary with their actual manager. Advice that touches their team's norms, priorities, or performance is their manager's territory. Staying in a peer-advisor lane means flagging that explicitly rather than quietly overriding it.
• No natural checkpoint. A direct-report relationship gets reviewed on a cycle; an informal one only continues as long as both sides keep choosing it, so it's worth periodically checking whether it's still useful rather than assuming it is.

Worked example

A colleague on a different team reached out about a specific hard decision they were facing. The first move was an explicit, opt-in question rather than assuming continued access: whether they wanted a recurring conversation or just help with this one thing. Advice stayed framed as "here's what I'd weigh" rather than a recommendation to just do X, and anything that touched their team's priorities or their manager's likely call was flagged as outside this lane, with a suggestion to raise it with their manager directly instead. A few sessions in, a light check-in confirmed it was still useful before continuing.

Trade-offs and pitfalls

A common mistake is treating an informal mentee like a direct report: being directive, assuming continued access, and not checking whether it's still wanted. The more durable version treats it as an ongoing, consent-based relationship, and requires being comfortable that some advice will simply be ignored with no way to enforce it, which is normal here, not a sign of failure. The other real pitfall is overstepping into another manager's territory, giving performance-adjacent feedback that should go through the person's actual chain instead.

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.