Business Context and Metrics Understanding Questions

Understand the broader business context for technical or operational work and identify relevant performance metrics. This includes recognizing the key performance indicators for different functions, translating technical outcomes into business impact, scoping a problem with success metrics and constraints, and using metrics to prioritize trade offs. Candidates should demonstrate how they would frame a problem in business terms before proposing technical or operational solutions.

MediumTechnical

59 practiced

Given these tables users(user_id int, signup_date date), orders(order_id int, user_id int, amount numeric, order_date date), payments(payment_id int, order_id int, status varchar where 'success' or 'failed'), write an efficient SQL query to identify users who made their first purchase in the last 30 days and then had no further successful purchases in the subsequent 90 days (i.e., churned). Explain any assumptions.

Sample Answer

Approach: find each user’s first successful order date, filter those first purchases to the last 30 days, and then ensure there are no additional successful payments for that user in the 90-day window after that first-success date.

Assumptions:- A “purchase” is an order with a payment.status = 'success'.- If an order has multiple payments, any successful payment counts the order as successful.- Dates are compared using order_date; payments link to orders via order_id.- “Last 30 days” is relative to CURRENT_DATE.

SQL:

sql

WITH successful_orders AS (
  -- mark orders that had at least one successful payment
  SELECT o.order_id, o.user_id, o.order_date
  FROM orders o
  JOIN payments p ON p.order_id = o.order_id AND p.status = 'success'
  GROUP BY o.order_id, o.user_id, o.order_date
),
first_success AS (
  -- user's first successful order date
  SELECT user_id, MIN(order_date) AS first_success_date
  FROM successful_orders
  GROUP BY user_id
),
candidates AS (
  -- users whose first success was within last 30 days
  SELECT f.user_id, f.first_success_date
  FROM first_success f
  WHERE f.first_success_date >= CURRENT_DATE - INTERVAL '30 days'
)
-- keep only users with no further successful orders in the next 90 days
SELECT c.user_id, c.first_success_date
FROM candidates c
LEFT JOIN successful_orders s
  ON s.user_id = c.user_id
  AND s.order_date > c.first_success_date
  AND s.order_date <= c.first_success_date + INTERVAL '90 days'
WHERE s.order_id IS NULL;

Notes:- This is index-friendly if orders(order_date, user_id) and payments(order_id, status) are indexed.- Edge cases: users with same-day multiple orders — those count as further purchases if order_date > first_success_date; change to >= if you want to treat same-day later orders as subsequent.

EasyBehavioral

80 practiced

Tell me about a time when you translated a technical or product metric into a clear business impact for non-technical stakeholders. Use the STAR format: describe the Situation, Task, Action (how you transformed/visualized the metric), and Result (business outcome and how stakeholders used it). Include tools you used and any validation steps.

MediumTechnical

115 practiced

Using transactions(transaction_id, user_id, amount, occurred_at), write SQL (standard SQL / BigQuery) to compute monthly cohort LTV: for each cohort month compute cumulative revenue per user for days 0–90 (first 90 days). Show key assumptions and how you would handle refunds or negative transactions.

Sample Answer

Approach:- Define cohort_month as the month of a user’s first transaction.- For each user, compute day_number = DATE_DIFF(transaction_date, cohort_date, DAY) and keep transactions within 0–90.- For each user-day compute cumulative revenue since day 0 up to that day (includes negative amounts for refunds = net revenue).- Aggregate across users in the cohort to get average cumulative revenue per user (LTV) for each day 0–90.

SQL (BigQuery / standard SQL):

sql

WITH first_tx AS (
  -- cohort per user = first transaction date (month)
  SELECT
    user_id,
    MIN(DATE(occurred_at)) AS cohort_date,
    DATE_TRUNC(MIN(DATE(occurred_at)), MONTH) AS cohort_month
  FROM transactions
  GROUP BY user_id
),
tx AS (
  -- join transactions to cohort and compute day offset
  SELECT
    t.transaction_id,
    t.user_id,
    DATE(t.occurred_at) AS tx_date,
    t.amount,
    f.cohort_month,
    f.cohort_date,
    DATE_DIFF(DATE(t.occurred_at), f.cohort_date, DAY) AS day_number
  FROM transactions t
  JOIN first_tx f USING (user_id)
  WHERE DATE_DIFF(DATE(t.occurred_at), f.cohort_date, DAY) BETWEEN 0 AND 90
),
user_day_revenue AS (
  -- aggregate per user/day (net amount; refunds are negative amounts)
  SELECT
    user_id,
    cohort_month,
    day_number,
    SUM(amount) AS day_amount
  FROM tx
  GROUP BY user_id, cohort_month, day_number
),
-- ensure every user has rows for days with zero revenue (so cumulative works)
user_days AS (
  SELECT
    ud.user_id,
    ud.cohort_month,
    d.day_number,
    COALESCE(ud.day_amount, 0) AS day_amount
  FROM (
    SELECT user_id, cohort_month FROM first_tx
  ) u
  CROSS JOIN UNNEST(GENERATE_ARRAY(0,90)) AS d(day_number)
  LEFT JOIN user_day_revenue ud
    ON ud.user_id = u.user_id AND ud.cohort_month = u.cohort_month AND ud.day_number = d.day_number
),
user_cumulative AS (
  -- cumulative revenue per user up to each day
  SELECT
    user_id,
    cohort_month,
    day_number,
    SUM(day_amount) OVER (PARTITION BY user_id, cohort_month ORDER BY day_number
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_revenue
  FROM user_days
)
-- cohort-level LTV: average cumulative revenue per user in cohort for each day
SELECT
  cohort_month,
  day_number,
  ROUND(AVG(cumulative_revenue), 4) AS ltv_per_user
FROM user_cumulative
GROUP BY cohort_month, day_number
ORDER BY cohort_month, day_number;

Key assumptions and handling refunds:- amount is net amount with positive purchases and negative refunds. This query treats refunds as negative amounts that reduce cumulative revenue (recommended for net LTV).- If you want gross revenue and separate refunds, add columns SUM(CASE WHEN amount>=0 THEN amount ELSE 0 END) and SUM(CASE WHEN amount<0 THEN amount ELSE 0 END) and compute metrics separately.- Cohort defined by first transaction date; you can change to first paying transaction by filtering amount>0 when computing first_tx.- Users without transactions after day 0 are included (counts as zero revenue) so average LTV divides by all users in cohort.Edge cases:- timezone normalization of occurred_at to DATE- very large cohorts — consider pre-aggregating and partitioning- users with multiple accounts: deduplicate by canonical user_id before cohorting.

MediumTechnical

57 practiced

How do you identify metrics that are unstable due to low sample size, and what statistical techniques or product/design changes would you employ to make them more reliable or to prevent misleading conclusions? Include recommendations for how to display uncertainty in dashboards.

Sample Answer

Start by detecting low-sample instability- Automatically flag series with small n (absolute count) or low event rate (e.g., <30 successes for proportion-based metrics).- Track metric variance vs sample size: high variance + low n is a red flag. Use control charts (CUSUM or EWMA) to spot excessive noise.- Monitor statistical power: compute detectable effect size given current n; if MDE >> business-relevant lift, treat results as underpowered.

Statistical techniques to stabilize estimates- Use appropriate CIs: Wilson or Agresti–Coull intervals for proportions (better coverage at small n); bootstrap CIs for complex metrics.- Bayesian shrinkage (hierarchical priors or James–Stein) to pull noisy estimates toward group means—reduces false extremes.- Aggregate or smooth: combine adjacent time windows, cohort aggregation, or exponential smoothing when appropriate.- Pre-specify sequential testing rules or use alpha-spending to avoid false positives from repeated looks.- Control false discovery rate (Benjamini–Hochberg) when tracking many metrics.

Product / design changes- Increase sample size: longer collection, wider rollouts, or targeted sampling to boost event counts.- Improve instrumentation: ensure events are captured reliably (reduce missingness) and define metrics to be less sparse (e.g., use “weekly active users” instead of “daily” for low-volume cohorts).- Introduce guardrail metrics (exposure counts) and minimum-n checks to prevent decisions on unstable slices.

Dashboard recommendations to display uncertainty- Always show n next to metric and require a minimum-n visual rule: grey out or show a warning if below threshold.- Display 95% CI as error bars or shaded bands on time series; for proportions use Wilson intervals.- Show MDE or power indicator for A/B metrics so stakeholders see what effects are detectable.- Add tooltip explanations: “This estimate has high uncertainty due to n=12; CI: [x,y].”- Use visual cues: color-coding (red/orange) for unstable points, and option to aggregate (toggle daily↔weekly).- Avoid over-reliance on p-values; present effect size with CI and business context.

Example: For a conversion rate with 15 conversions/day, display the point estimate, Wilson 95% CI, n, and a badge “Underpowered — MDE 40%”; recommend aggregating to weekly or running longer to reach n≥100.

These practices reduce misleading conclusions while keeping stakeholders informed about uncertainty.

MediumTechnical

61 practiced

You have an A/B test with p<0.05 showing a 2% lift in conversion. Describe how you would translate this statistical result into a business decision: estimate absolute revenue impact given baseline conversion and ARPU, check for heterogeneity of treatment effect, consider risk and operational constraints, and outline recommended next steps.

Unlock Full Question Bank

Get access to hundreds of Business Context and Metrics Understanding interview questions and detailed answers.

Join thousands of developers preparing for their dream job.