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

I track coachability by converting qualitative feedback into objective, time-series metrics that show both responsiveness and lasting improvement. Key metrics/artifacts I would use:

1. Time-to-close feedback tickets

• What: median time from stakeholder feedback submission to acknowledged fix or revision.
• How to gather: log feedback in Jira/Asana or a dedicated form; capture timestamps for submission, acknowledgement, and closure.
• Why: shortens with good coachability (I act quickly on feedback).

2. Reduction in repeated feedback

• What: % of feedback items that recur for the same dashboard/report within 90 days.
• How to gather: tag tickets or comments with feedback categories; run weekly SQL to count repeats.
• Why: shows whether I internalize feedback vs. applying temporary fixes.

3. Stakeholder satisfaction / peer ratings

• What: structured 5-point ratings on clarity, usefulness, and responsiveness after major deliveries.
• How to gather: automated short surveys (Google Forms, Typeform) triggered post-delivery; anonymized to reduce bias.
• Why: captures perceived improvement in collaboration and deliverables.

4. Data quality pass rate & incident frequency

• What: % of scheduled reports passing validation tests and count of production data incidents tied to my artifacts.
• How to gather: automated tests in CI/CD for ETL, logging incidents in an incident tracker.
• Why: coachability includes adopting best practices that reduce errors.

5. Adoption and usage uplift

• What: change in active users, dashboard view time, and key metric saves after iterations.
• How to gather: tool usage analytics (Tableau/Power BI usage logs).
• Why: demonstrates that feedback-driven changes increased business value.

Operationalize: capture these in a monthly “growth” dashboard, set SMART targets (e.g., reduce repeated feedback by 30% in 3 months), and review with manager/mentor to validate causation and set next learning actions.

High-level goal: determine whether “engagement is down” is a real change, an artifact, or driven by specific segments/channels — and surface likely drivers for follow-up experiments.

Step 0 — clarify & gather:

• Clarify KPIs (DAU/MAU, session length, conversion, retention, pages/session), time window, and data sources (prod events, marketing, logs).
• Confirm data quality: missingness, schema changes, event drops, timezone shifts, sampling.

Exploratory sequence (ordered):

1. Global sanity & context

• Time series (daily/weekly) of primary KPIs for last 12+ months with 7‑day rolling avg. Purpose: spot trend vs seasonality.
• Annotate with product releases, campaigns, tracking changes.

2. Validate signal vs artifact

• Compare raw event counts vs unique users; check instrumentation gaps (event drop, client SDK versions).
• Run missingness heatmap by event and day.

3. Trend vs anomaly decomposition

• STL decomposition (seasonality/trend/residual) and change‑point detection to identify start of decline.
• Compare year-over-year and week-over-week deltas.

4. Segmentations to localize

• Cohort retention curves (by signup week/device/app version).
• Funnel conversion rates by channel, landing page, device, geography, user persona.
• User-level distributions (session length, sessions/user) with violin/ECDF plots to see whether a minority drives change.
• New vs returning users, paid vs free, marketing UTM segments.

5. Attribution & correlation

• Correlate KPI dips with upstream signals: paid spend, ad impressions, backend errors, response latency.
• Cross-correlation (lag analysis) to find leading indicators.

6. Statistical checks

• Hypothesis tests: t-test/Mann-Whitney on pre/post-period segments (accounting for autocorrelation) and bootstrap CIs for metric differences.
• Regression / difference‑in‑differences controlling for seasonality and channels to estimate effect size.
• Multiple testing correction when scanning many segments.

7. Drill into likely drivers

• If a segment (e.g., Android app vX) shows steep drop: compare crash rates, retention, and UX flows for that cohort; prioritize session replay / logs.
• If channel-level drop: inspect campaign changes, landing page A/B tests, tracking UTM mismatches.

Deliverables & dashboard:

• Executive dashboard: headline metric trend, YoY / WoW delta, top 3 segments driving decline (by attributable %), instrumentation health.
• Investigative dashboard: interactively filter by cohort, funnel, device, version; enable export of user-level samples for qualitative review.

Next steps:

• Propose targeted experiments or rollback (if instrumentation or release implicated) and set automated anomaly alerts on leading indicators.

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.

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.

Attribute each purchase's revenue by joining it to the buyer's first and last marketing touchpoint, then aggregate by channel under each model separately: a UNION ALL keeps first-touch and last-touch as two labeled result sets rather than blending them into one number. First-touch credits whichever channel started the relationship; last-touch credits whichever channel closed it. Neither model is more correct on its own: a stakeholder who owns awareness and top-of-funnel spend wants first-touch, while a stakeholder optimizing bottom-of-funnel channels (retargeting, paid search bidding) wants last-touch.

Approach

1. Rank a user's touchpoints by time, once ascending (to find the first touch) and once descending (to find the last touch), using ROW_NUMBER() partitioned by user.
2. Take the rn = 1 row from each ranking as that user's first-touch and last-touch channel.
3. LEFT JOIN purchases to each of those two lookups (not INNER JOIN), so a purchase from a user with zero recorded touchpoints still appears, labeled "unknown," instead of silently disappearing from the total.
4. UNION ALL the two attributed sets, tagging each with an attribution_model label, then GROUP BY model and channel.

Handling ties. If two touchpoints share the exact same touch_time for a user (duplicate event logging, same-second clickstream events), ordering by touch_time alone leaves ROW_NUMBER() unstable: which row lands on rn = 1 can differ between runs. Add a deterministic tiebreaker to the ORDER BY: a touchpoint primary key or ingestion sequence, not another text column like channel, since channel names just sort alphabetically and have nothing to do with which touchpoint actually happened first. The worked example below gives touchpoints a touchpoint_id surrogate key for exactly this purpose and includes a genuine cross-channel tie (user 6) to show it resolving deterministically.

When a stakeholder prefers which model. First-touch fits brand and awareness marketing ("which channel introduces us to buyers"); last-touch fits performance marketing and channels billed on last-click, like paid search bidding ("which channel closes the sale"). Neither handles split credit across a multi-touch path; a stakeholder who needs that is really asking for a fractional or time-decay model, which requires accumulating credit across the whole path, not just the min/max touch.

Worked example

Seed data and query (SQLite):

CREATE TABLE touchpoints (
  touchpoint_id INTEGER PRIMARY KEY,
  user_id     INTEGER,
  channel     TEXT,
  touch_time  TEXT
);

CREATE TABLE purchases (
  purchase_id INTEGER PRIMARY KEY,
  user_id     INTEGER,
  purchase_time TEXT,
  revenue     NUMERIC
);

INSERT INTO touchpoints (touchpoint_id, user_id, channel, touch_time) VALUES
  (1, 1, 'google',  '2026-01-01'),
  (2, 1, 'email',   '2026-01-03'),
  (3, 1, 'organic', '2026-01-05'),
  (4, 2, 'social',  '2026-01-02'),
  (5, 3, 'email',   '2026-01-01'),
  (6, 3, 'email',   '2026-01-01'),
  (7, 6, 'zeta',    '2026-01-01'),
  (8, 6, 'alpha',   '2026-01-01');

INSERT INTO purchases VALUES
  (101, 1, '2026-01-06', 100),
  (102, 2, '2026-01-03', 50),
  (103, 3, '2026-01-02', 75),
  (104, 4, '2026-01-02', 30),
  (105, 6, '2026-01-02', 60);

WITH ranked AS (
  SELECT
    user_id, channel, touch_time, touchpoint_id,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY touch_time ASC,  touchpoint_id ASC) AS rn_first,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY touch_time DESC, touchpoint_id ASC) AS rn_last
  FROM touchpoints
),
first_touch AS (SELECT user_id, channel FROM ranked WHERE rn_first = 1),
last_touch  AS (SELECT user_id, channel FROM ranked WHERE rn_last  = 1),
attributed AS (
  SELECT p.user_id, p.revenue, COALESCE(ft.channel, 'unknown') AS channel, 'first_touch' AS attribution_model
  FROM purchases p
  LEFT JOIN first_touch ft ON p.user_id = ft.user_id
  UNION ALL
  SELECT p.user_id, p.revenue, COALESCE(lt.channel, 'unknown') AS channel, 'last_touch' AS attribution_model
  FROM purchases p
  LEFT JOIN last_touch lt ON p.user_id = lt.user_id
)
SELECT attribution_model, channel, SUM(revenue) AS total_revenue
FROM attributed
GROUP BY attribution_model, channel
ORDER BY attribution_model, total_revenue DESC;

Result:

┌───────────────────┬─────────┬───────────────┐
│ attribution_model │ channel │ total_revenue │
├───────────────────┼─────────┼───────────────┤
│ first_touch       │ google  │ 100           │
│ first_touch       │ email   │ 75            │
│ first_touch       │ zeta    │ 60            │
│ first_touch       │ social  │ 50            │
│ first_touch       │ unknown │ 30            │
│ last_touch        │ organic │ 100           │
│ last_touch        │ email   │ 75            │
│ last_touch        │ zeta    │ 60            │
│ last_touch        │ social  │ 50            │
│ last_touch        │ unknown │ 30            │
└───────────────────┴─────────┴───────────────┘

User 1's three touches (google, then email, then organic) split their $100 purchase: google gets credit under first-touch, organic gets credit under last-touch. User 2 and user 3 have only one touch each, so both models agree for them. User 4 purchased with no logged touchpoint at all: both models correctly bucket that $30 as "unknown" rather than dropping it (an INNER JOIN would have silently dropped it) or guessing a channel.

User 6 is the real tiebreak case: two touchpoints logged the same second, in different channels ('zeta', touchpoint_id = 7, and 'alpha', touchpoint_id = 8). Alphabetically 'alpha' sorts before 'zeta', so a channel-based tiebreaker would hand the win to 'alpha'; but touchpoint_id is what actually orders them, and touchpoint 7 ('zeta') was ingested first, so 'zeta' wins the tie under both rankings here, exactly the deterministic-by-ingestion-order behavior the tiebreaker is supposed to produce, and the opposite of what alphabetizing on channel would have given.

Trade-offs & pitfalls

• Complexity: two window-function sorts over touchpoints (O(n log n) each) plus a UNION ALL that doubles the row count of purchases. For a very large purchases table, consider whether both breakdowns are actually needed in one query or would be cheaper as two simpler queries.
• Edge cases: users with zero touchpoints (handled by LEFT JOIN + COALESCE, not INNER JOIN); duplicate/tied touch_time values, resolved deterministically here by touchpoint_id, not by channel (demonstrated with user 6 above), since without a real tiebreaker "first touch" would silently change between runs; a touchpoint logged after the purchase itself, since this simple model doesn't check touch_time against purchase_time (add WHERE touch_time <= purchase_time before ranking if that ordering matters to the business).
• Common wrong turn: writing two separate queries, one per model, and never combining them, so the interviewer has to ask for a single result set. Also common: using INNER JOIN instead of LEFT JOIN, which silently drops purchases from untouched users and understates total attributed revenue without any error or warning.