Lyft Business Intelligence Analyst (Entry Level) - Comprehensive Interview Preparation Guide

Direct answer

A dashboard that misled users is specific, useful feedback, not a vague complaint: it means something in the visual encoding or interaction design led people to a wrong conclusion, and the fix is identifying exactly what caused the misread, iterating with real users in the loop, and validating the fix actually improved comprehension rather than just looking cleaner.

Structured elaboration

• Visualization principles and interactions to change: common culprits are a truncated or non-zero axis exaggerating a difference, a color scale that implies an order that isn't there, an aggregate number shown without the underlying variance or sample size, or a default filter state that quietly excludes relevant data without saying so.
• Iterating: don't jump straight to a final redesign, sketch two or three candidate fixes, get quick reactions from a few actual users, and refine before shipping broadly.
• Validating comprehension: show the revised dashboard to people who weren't involved in building it and ask them to state, in their own words, what it's telling them; if that answer matches what the data actually shows, the fix worked.

Worked example

As a Business Intelligence Analyst, a product owner told me a sales dashboard had led a stakeholder to believe a metric had collapsed when it had actually just returned to a normal seasonal level, because the y-axis started well above zero, exaggerating the visual drop. I changed the axis to start at zero and added a lighter reference band showing the same period in prior years, so a viewer could see the current dip in seasonal context rather than in isolation. I also noticed the default view silently filtered to one region without a visible label, which I fixed by making the active filter always shown on screen. I sketched two versions of the redesign and showed them to two other analysts and one stakeholder outside my team, asking them to describe what the chart was telling them; the seasonal-band version was the one where every person correctly described it as a normal seasonal dip, so that's the one I shipped, and I followed up two weeks later with the original stakeholder to confirm the revised view matched their read of the business situation.

Trade-offs and pitfalls

The pitfall is redesigning based only on your own judgment of what looks clearer, without checking whether real viewers actually read it correctly, since a chart can look objectively cleaner and still mislead in the same way. The other is over-correcting into a dashboard so dense with context and caveats that it's harder to read at a glance, which trades one problem for another.

Approach: build weekly cohorts by install week + channel, generate week offsets 0..12, join transactions attributed to the user’s install channel and compute: retention_rate = active_users_in_week / cohort_size, cumulative_ltv = cumulative revenue per user from week 0 through that week.

Assumptions:

• Attribution: each transaction is attributed to the channel on the user's install row.
• Cohort key: install_week is date_trunc('week', install_date) (Mon-start); adjust if you want Sunday.
• We measure week_number = floor((tx_date - install_date)/7 days). Keep only 0..12 (13-week window).
• Retention: a user is “retained” in week w if they have >=1 transaction in that week. Week 0 includes transactions on install week.
• Cumulative LTV is total revenue per cohort divided by cohort size up to that week.

Postgres SQL (adjust date functions for other dialects):

WITH installs_cohort AS (
  SELECT
    user_id,
    date_trunc('week', install_date)::date AS install_week,
    channel,
    install_date
  FROM installs
),
week_series AS (
  SELECT generate_series(0,12) AS week_number
),
-- transactions attributed to user install channel and compute week offset
tx_enriched AS (
  SELECT
    i.install_week,
    i.channel,
    i.user_id,
    t.tx_date::date AS tx_date,
    t.amount,
    FLOOR(EXTRACT(EPOCH FROM (t.tx_date::date - i.install_date)) / 86400 / 7)::int AS week_number
  FROM installs_cohort i
  LEFT JOIN transactions t
    ON t.user_id = i.user_id
  WHERE t.tx_date IS NULL OR t.tx_date::date >= i.install_date::date
),
-- cohort sizes
cohort_sizes AS (
  SELECT install_week, channel, COUNT(DISTINCT user_id) AS cohort_size
  FROM installs_cohort
  GROUP BY 1,2
),
-- per-cohort-week metrics: active users (>=1 tx in that week) and revenue in that week
cohort_week AS (
  SELECT
    i.install_week,
    i.channel,
    w.week_number,
    cs.cohort_size,
    COUNT(DISTINCT CASE WHEN te.week_number = w.week_number THEN te.user_id END) AS active_users,
    COALESCE(SUM(CASE WHEN te.week_number = w.week_number THEN te.amount END),0) AS revenue_in_week
  FROM (SELECT DISTINCT install_week, channel FROM installs_cohort) i
  CROSS JOIN week_series w
  LEFT JOIN tx_enriched te
    ON te.install_week = i.install_week
    AND te.channel = i.channel
    AND te.week_number BETWEEN 0 AND 12
  LEFT JOIN cohort_sizes cs
    ON cs.install_week = i.install_week AND cs.channel = i.channel
  GROUP BY 1,2,3,4
),
-- cumulative revenue up to each week per cohort
cohort_cume AS (
  SELECT
    cw.install_week,
    cw.channel,
    cw.week_number,
    cw.cohort_size,
    cw.active_users,
    cw.revenue_in_week,
    SUM(cw.revenue_in_week) OVER (PARTITION BY cw.install_week, cw.channel ORDER BY cw.week_number ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_revenue
  FROM cohort_week cw
)
SELECT
  install_week,
  channel,
  week_number,
  CASE WHEN cohort_size = 0 THEN 0.0 ELSE active_users::numeric / cohort_size END AS retention_rate,
  CASE WHEN cohort_size = 0 THEN 0.0 ELSE cumulative_revenue::numeric / cohort_size END AS cumulative_ltv
FROM cohort_cume
ORDER BY install_week, channel, week_number;

Notes / alternatives:

• If you want retention based on any activity (not just transactions), join an events table instead.
• For large tables, pre-aggregate transactions by user-week to reduce joins and improve performance.
• If your SQL dialect lacks generate_series, create a small numbers table for weeks 0..12.

Direct answer. Move the per-row scalar computation out of the SELECT list and into a JOIN with a GROUP BY, so the database computes the aggregate once per outer-table row via a single pass, instead of re-scanning the inner table separately for every outer row.

Structured elaboration. A correlated scalar subquery in the SELECT list runs once PER ROW of the outer query, each time re-scanning (or re-probing an index on) the inner table for that one row's value. A LEFT JOIN followed by GROUP BY instead lets the engine compute the whole join and its aggregation together, typically as a single hash or merge pass over both tables, which scales much better as the outer table grows, because the cost stops being "outer rows times inner-table lookup cost" and becomes closer to "size of both tables, scanned once each."

Worked example. customers(1),(2),(3). orders(order_id=100, customer_id=1), (101, customer_id=1), (102, customer_id=2).

-- correlated subquery: one subquery execution per customer row
SELECT c.customer_id,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS orders_count
FROM customers c;

-- equivalent join + aggregation: one pass over both tables
SELECT c.customer_id, COUNT(o.order_id) AS orders_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id;

Both return the identical result: (1, 2), (2, 1), (3, 0). Note the LEFT JOIN (not INNER) and COUNT(o.order_id) (not COUNT()) are both required to correctly get 0 for customer 3, who has no orders at all; getting either one wrong (an INNER JOIN, or COUNT() instead of COUNT(order_id)) would silently drop customer 3 or overcount.

Trade-offs and pitfalls. The rewrite is only correct if you're careful about two things the original subquery handled implicitly: use LEFT JOIN (not INNER) if rows with zero matches should still appear, and use COUNT(specific_column) rather than COUNT(*) so that a customer with zero orders (whose joined columns are all NULL) counts as 0 rather than 1. The correlated-subquery form remains readable and perfectly fine for a handful of rows or a one-off analysis; the join rewrite earns its complexity specifically at scale, where the per-row subquery cost compounds.

A skeleton to fill in. Situation: briefly set the stakes and the deadline pressure that made waiting for complete data not viable. Task: state the specific decision you had to make and name the specific missing piece, not a vague sense of uncertainty. Action, in four parts: (a) exactly what you identified as missing, named precisely rather than described as "incomplete data"; (b) the explicit assumption you wrote down to fill that gap, specific enough that someone else could check it; (c) a minimal, cheap, fast check or small experiment you ran to reduce the risk of that assumption before committing, not a full study; (d) the decision you actually made. Result: a real outcome with real metrics, plus a lesson specific enough that it changed a subsequent behavior, a checklist item, a threshold, not a generic "I learned to communicate more."

A fully worked instance. Situation: six weeks before a contractual customer commitment date in March of a product's second year, a team had to decide whether to ship a new PDF export feature using on-the-fly rendering or invest in a pre-rendering pipeline, with no data yet on how many customers would need large (500-plus row) exports, the case that would make on-the-fly rendering too slow. Task: decide by March 15 which architecture to commit two engineers and four available weeks to, ahead of the deadline.

Action: the missing piece was named precisely, not "usage data" broadly but specifically the distribution of export sizes among the 40 customers slated to get this feature at launch, which did not exist because the feature had not shipped yet. The assumption was written into the design document explicitly: "assume export size distribution mirrors the existing comma-separated-values (CSV) export feature's distribution, median 80 rows, 95th-percentile (p95, meaning the value only 5% of cases exceed) 600 rows, flagged as unverified for this feature's specific customer behavior." The minimal check: instrumented the existing CSV export with row-count logging for 10 days, half a day of engineering work, which had never existed before. It returned a real, if adjacent, number: observed p95 was actually 850 rows, higher than the assumption. Given that, the team chose the pre-rendering pipeline (the full four weeks), scoped to handle up to 2,000 rows with headroom, with the assumption and the 10-day proxy data documented as the basis, plus an explicit note to revisit the choice if post-launch usage came in far lower.

Result: shipped on time, April 26, for the customer deadline. At 60 days, actual PDF export p95 was 720 rows, within range of the proxy-informed decision. Had the team instead sized on-the-fly rendering to the original 600-row assumption, roughly 15% of customers would have hit timeouts on their largest exports, based on the 60-day distribution. Lesson, stated specifically: the team adopted a standing practice, now a named step in the design-document template ("proxy data check"), of instrumenting the nearest existing analog feature for at least a week before committing to architecture on anything with no usage history, rather than debating projected usage in a meeting.

A shorter version of the same shape shows up outside engineering too. A support-operations lead deciding whether to build a dedicated escalation queue for a new product line, with no historical ticket data for it yet, names the same missing piece precisely (ticket volume for the new line specifically, not "support load" broadly), writes down the assumption (using the closest existing product line's ticket-per-customer rate as a stand-in), runs the same kind of minimal check (tags and counts the first two weeks of real tickets after launch instead of waiting for a full quarter), and adopts the same kind of lasting change (a launch-checklist item: instrument ticket tagging before day one, not after volume becomes a problem).

What separates a strong answer from a mediocre one. A mediocre answer names a real decision and says "we didn't have data, so we made our best guess and it worked out," with no visible mechanism (no named assumption, no cheap validation step) and a vague, unfalsifiable lesson. A strong answer shows the exact missing input, a specific and checkable assumption, real evidence gathered before committing (even if small and imperfect), and a lesson concrete enough that it became a repeatable practice. Interviewers are listening for whether you distinguish "no data" from "no test of the assumption," since the latter is what's actually within your control under time pressure.

Direct answer

A retro after a release with repeated design-engineering friction should produce two things: an honest, specific account of where the handoff actually broke down, not a vague 'communication issues,' and a small number of concrete process changes, each with an owner and a way to tell in a quarter whether it worked. Running it well means separating fact-finding from diagnosis, and diagnosis from blame.

Structured elaboration

Design principles for the session

• Facts before diagnosis: start from a timeline of what actually happened (spec dates, handoff dates, bug counts, points where implementation and design diverged), not from opinions about who was at fault.
• Root cause, not the nearest symptom: 'engineering didn't follow the spec' is a symptom; the root cause might be that the spec didn't capture edge-case states, or that both sides were working from different versions of a shared design system mid-migration.
• Few, high-leverage commitments: two or three process changes people will actually do beat ten action items that quietly get dropped.
• Everyone leaves with the same understanding of what changed, not just what went wrong.

A workable structure

One illustrative shape, adaptable to a team's own rhythm:

What 'actually changes how the two teams work' looks like

The output isn't a list of intentions, it's a specific artifact or habit that exists after the meeting and didn't before: a shared checklist embedded in the handoff process, an automated check that catches a class of mismatch before it ships, or a standing short sync during implementation windows. Whatever it is, it needs a way to tell if it worked, not just that it happened.

Worked example

One team's root cause turned out to be that design tokens (colors, spacing values) were maintained in the design tool but hand-copied into code, so drift was inevitable and nobody could tell which side was 'correct' when they disagreed. The concrete fix was an automated export from the design tool into the codebase, checked by both a design reviewer and a frontend reviewer before merge, plus a short recurring sync during active implementation. A quarter later, the team had a real signal that it worked: noticeably fewer visual-mismatch comments on pull requests and less late-stage rework than the release that triggered the retro. The same root-cause pattern shows up in other domains as a hand-copied data contract or config value instead of a design token, so the same fix shape (automate the handoff, add a lightweight check, add a short sync during the risky window) generalizes well beyond design and engineering specifically.

Trade-offs and pitfalls

• A retro that produces ten action items usually produces zero completed ones; prioritizing ruthlessly matters more than being thorough.
• If the room jumps straight to solutions or blame instead of facts first, the real root cause, often structural or tooling-related rather than a person's failure, never surfaces.
• A retro that isn't revisited becomes theater. Put the check-in on the calendar before the room disperses, not as a vague intention afterward.
• Watch for a fix that only addresses this specific release's symptom (a one-off manual double-check) rather than the structural cause; it holds for one cycle and then quietly stops happening.

Direct answer. SELECT * pulls every column regardless of what the query actually needs, which increases network transfer, defeats the possibility of an index-only scan (since the index almost never contains every column), and silently breaks if the table's column set changes; rewrite it to name only the columns the caller actually uses.

Structured elaboration. Three distinct costs stack up. First, I/O and network: every extra column is extra bytes read from storage and sent over the wire, even for columns the caller immediately discards, which matters most for wide tables or ones with large text/JSON columns. Second, index eligibility: an index-only scan requires every needed column to be present in the index; asking for every column in the table makes that essentially impossible for any index narrower than the full row, forcing a heap visit that a narrower SELECT might have avoided. Third, fragility: if the table gains a column later, every SELECT * consumer starts receiving it whether or not it's ready to, which has broken more than one downstream integration in ways that are hard to trace back to the schema change that caused it.

Worked example. For transactions(transaction_id, user_id, amount, currency, created_at, status, metadata jsonb), a dashboard that only needs the four most recent completed transactions' amount and date has no business fetching the metadata JSONB column at all:

-- anti-pattern: pulls every column, including a large JSONB payload
SELECT * FROM transactions
WHERE status = 'completed'
ORDER BY created_at DESC
LIMIT 100;

-- production-safe: only the columns the caller actually uses
SELECT transaction_id, amount, created_at
FROM transactions
WHERE status = 'completed'
ORDER BY created_at DESC
LIMIT 100;

The rewrite reduces network payload substantially (dropping metadata, currency, status, and user_id from the wire format) and makes it possible, if status and created_at were part of a covering index that also included transaction_id and amount, for the query to be served entirely from that index.

Trade-offs and pitfalls. Naming columns explicitly is marginally more code to write and to keep in sync as requirements change, which is the entire reason SELECT * remains tempting; treat that maintenance cost as strictly smaller than the recurring, compounding cost of over-fetching on every single execution of a query that runs often.

Complexity

The change doesn't alter the query's algorithmic shape; it changes the constant factor on I/O and network transfer per row, and can change whether an index-only path is even available at all.

Edge cases

A table with a genuinely small number of columns, all of which the caller uses anyway, gets little practical benefit from this rewrite; the cost matters most on wide tables or ones with large variable-length columns like JSON or text blobs.

Direct answer
A chart using 12 colors, 3 axes, overlapping lines, no axis labels, and a rainbow palette fails on nearly every principle of clear encoding at once; the fix is to cut the series count, pick one axis per unit of measurement, label everything directly, and replace the rainbow palette with a small categorical or sequential palette matched to the data's actual structure.

Structured elaboration
Six concrete problems and their fixes:

1. Too many series (12 colors): past about 6-8 distinct lines, colors become indistinguishable. Fix: keep the 3-4 series that matter, move the rest to "other" or a drill-down, or switch to small multiples (one mini-chart per series).
2. Three axes: more than two axes (and ideally just one) makes it impossible to know which line maps to which scale. Fix: one axis per unit; if units genuinely differ, use small multiples instead of overlaying.
3. Overlapping lines: dense overlap hides individual series. Fix: reduce series count (as above) or use a small-multiples grid.
4. No axis labels: the chart is uninterpretable without units and time range. Fix: label both axes with units and a time range in the title or subtitle.
5. Rainbow palette: implies false ordering and clashes visually. Fix: a categorical palette of 4-6 distinguishable hues for categories with no order, or a sequential palette for ordered/quantitative series.
6. No annotation of the key insight: even a clean chart still needs a headline for an executive briefing.

Worked example
A revised version for an executive briefing: keep this a time-series comparison (the data is inherently a trend over time), rendered as a decluttered multi-line chart, but with only the top 3 series by magnitude, a single y-axis, direct end-of-line labels instead of a legend, a 3-4 color categorical palette, axis labels with units, and one annotation naming the key takeaway (e.g. "Channel A overtook Channel B in March"). If the audience's actual question is a snapshot comparison rather than a trend (e.g. "who is winning right now"), a sorted horizontal bar chart of the same top 3-4 series is the better chart-type choice instead of a line chart.

Trade-offs and pitfalls
Cutting to 3-4 series means some information is genuinely lost; disclose that the remaining series were grouped into "other" rather than silently dropping them, and offer a drill-down link for anyone who needs the full breakdown.

Direct answer: Structure the walkthrough as problem framing, data, approach, validation, and then the actual business decision the output drove. Closing the loop back to a business action, not just a model metric, is the step interviewers probe hardest and the one most candidates skip.

The end-to-end shape interviewers expect

• Problem framing: what business question was open, why now, and what decision hinged on the answer.
• Data: source, rough order of magnitude, known quality issues, and how leakage (information about the outcome sneaking into the training features) or censoring (outcomes still unresolved or unknown when the data was pulled) was handled.
• Approach: a baseline first, then the model chosen and why, weighing simplicity against complexity rather than reaching for the fanciest option by default.
• Validation: how leakage was avoided (a time-based split is the most common fix), and a metric aligned to the business question, not an ML metric evaluated in isolation.
• The business decision: what actually changed because of the output. A model that scored well but changed nothing downstream is not a strong answer to this question.

ML metric versus business metric

Worked example (illustrative, arithmetic shown)

Problem: predicting which support tickets will breach their SLA, to prioritize triage. Baseline: 100 of roughly 500 weekly tickets breach (20%). Historical analysis shows breaches cluster in tickets left unclaimed for over two hours during high queue depth. A gradient-boosted model is trained on time-since-open, category, and queue depth, validated on a later, held-out time window to avoid leakage from features that depend on future queue state. Business decision: route the top 100 risk-scored tickets each week to a dedicated priority lane. Illustrative assumption for the worked example: routing cuts the breach rate within that top-100 group from the baseline 20% to 5%. That's 100 x 0.20 = 20 baseline breaches in that group versus 100 x 0.05 = 5 after routing, a reduction of 15 breaches per week: a 75% drop from the baseline 20 breaches in that group, equal to about 3% of total weekly ticket volume (15 of the 500 weekly tickets).

Trade-offs and pitfalls

• Describing the model in detail but never naming the actual downstream decision or action is the single most common gap.
• Features that depend on future information (like final queue state) leak into training and inflate offline metrics; a time-based split is the standard fix.
• Reaching for model complexity before establishing a simple baseline makes the eventual lift hard to attribute.
• Validation-period performance and deployment-period performance can diverge as the underlying distribution drifts; not planning for that is a common oversight.

Direct answer

A north-star metric (NSM) is a single, clear measure that captures the core value your product delivers to customers and correlates with long-term business health. As the analyst for a subscription software-as-a-service (SaaS) company, I would run a repeatable process: align stakeholders on outcomes, shortlist candidates, score them against explicit criteria, validate the finalist with both data and stakeholder input, then govern it going forward.

Structured elaboration

Step-by-step selection process

1. Align: gather stakeholders (product, sales, customer success, executive) and agree on the outcomes the metric should serve (growth, retention, expansion).
2. Inventory: list every plausible candidate metric and its data source.
3. Score: filter candidates against explicit criteria (below) to a shortlist of two or three.
4. Validate quantitatively: run a historical correlation and lag check against business outcomes (worked below).
5. Validate qualitatively: confirm with user research and a stakeholder workshop that the metric reflects real customer value, not just a convenient number.
6. Decide and govern: assign an owner, define the formula and segmentation rules, and set a review cadence (typically monthly).
7. Re-evaluate: revisit quarterly or after a major product change.

Candidate metrics scored against selection criteria

Selection criteria (explicit)

• Reflects durable customer value, not just activity.
• Leading enough to inform a decision within the current quarter.
• Sensitive to real product changes without being dominated by noise.
• Has a clear owner and levers a team can actually pull.

Worked example

To validate "engaged sessions per user" (three or more completed core-action sessions in the trial user's first week) as a leading indicator worth anchoring the NSM to, pull a historical cohort of 1,000 trial users. 400 hit the threshold in week one and 600 did not.

The 5.3x conversion gap between engaged and non-engaged trial users, observed in week one and realized as paid conversions weeks later, is the evidence a validation exercise needs: it shows the candidate is both leading (visible before the outcome) and predictive of the outcome that ultimately funds the business, which is why it beats a coarser activity count like 28-day active users.

Trade-offs & pitfalls

• A single historical cohort shows correlation, not causation; a rigorous validation follows up with a randomized experiment (for example, an onboarding nudge that increases engaged sessions) and checks whether conversion actually moves.
• A leading indicator that sits too close to the outcome (such as "clicked the upgrade button") is easy to game and doesn't really diagnose anything upstream; the metric should sit before multiple independent product levers.
• MRR stays essential to track, just not as the north star: govern it alongside the NSM as the primary business metric, not instead of it.
• Re-validate the NSM after a major product pivot, since the "core action" defining engagement can shift underneath it.

Row-level calculations

• Definition: Computed for each data row before any aggregation (e.g., arithmetic on columns).
• BI example: Per-row profit = [Revenue] - [Cost] so each transaction has its profit.
• Order-of-operations: Evaluated first (data source → row-level calculated fields → then aggregations).
• Wrong-type pitfall: If you instead make Profit as an aggregate AVG([Revenue]) - AVG([Cost]) when you need per-transaction profit, downstream aggregations like SUM(Profit) will be incorrect because averaging before subtracting changes results.

Aggregate calculations

• Definition: Calculations that aggregate values across rows (SUM, AVG, MIN, MAX) and return a single value per partition.
• BI example: Average daily sales = AVG([Daily Sales]) or SUM([Sales]) aggregated by day.
• Order-of-operations: Evaluated after row-level fields and then filters like data source/row-level filters; aggregate filters (Context filters vs. dimension filters) affect them.
• Wrong-type pitfall: Using a table calculation (e.g., WINDOW_AVG) instead of an aggregate AVG at the data level can give wrong results when the view’s partitioning or level-of-detail differs—e.g., WINDOW_AVG across months instead of AVG per product leads to misleading averages.

Table calculations

• Definition: Computed after the data is returned to the view; they operate on the visible result set and can do running totals, percent-of-total, rankings.
• BI example: Running total of sales across months = RUNNING_SUM(SUM([Sales])).
• Order-of-operations: Last—calculated after data source, extracts, row-level calcs, joins, LODs, aggregations, and most filters (only table calc filters apply last).
• Wrong-type pitfall: If you try to compute a running total using a row-level running flag (e.g., cumulative logic per row) instead of a table calculation, it won’t respect the view ordering and partitioning—leading to incorrect cumulative numbers when data is aggregated in the view.

Quick guideline: use row-level for per-transaction logic, aggregates/LODs for group-level truths independent of the view, and table calcs for ordering/relative computations that depend on the visible result set and table layout.