Reference

45-Minute Senior Analytics Mock

Interview Mock Timer

Use this timer to simulate interview pressure. Prioritize correctness, then explain tradeoffs.

Duration

20:00

Senior mock combines SQL correctness, modeling judgment, and operational risk handling.

Time Split #

20 min SQL design and solution
15 min semantic/modeling tradeoffs
10 min incident and rollback follow-up

Hard Prompt #

Design monthly KPI outputs for revenue, return rate, and returning-customer share. Include metric contracts and one rollback scenario.

Required output columns (use these exact aliases):

month_start
gross_revenue
returned_revenue
net_revenue
return_rate
active_customers
retained_customers
returning_customer_share

Naming note: returning_customer_share divides retained customers by the current month's active customers. That is not classic retention, which divides by the prior-month cohort. Senior interviews dock points for calling this metric "retention".

Loading SQL editor...

Validation and Incident Check #

sql

-- Reconciliation check: order-level total vs item-level total
SELECT
  o.order_id,
  ROUND(o.order_total, 2) AS order_total,
  ROUND(SUM(oi.line_total), 2) AS item_total
FROM ecom_orders o
JOIN ecom_order_items oi
  ON oi.order_id = o.order_id
WHERE o.order_status = 'completed'
GROUP BY o.order_id, o.order_total
HAVING ROUND(o.order_total, 2) <> ROUND(SUM(oi.line_total), 2);

Metric Contract and Rollback: What a Strong Answer Covers #

The last 25 minutes are scored on judgment, not SQL. A strong answer covers:

Metric contract per KPI: name, grain (month), source tables, filters (completed orders only), and edge policy (e.g. "returned_revenue nets item-level returns into the order's month, not the return's month"), plus an owner and a versioned definition.
Denominator policy stated up front: every rate names its denominator and NULL-guards it; returning_customer_share is explicitly not prior-month-cohort retention.
Rollback scenario walked end to end: a definition change ships and finance totals diverge — detect via a reconciliation check (like the order-vs-item query above), quantify blast radius (which months and dashboards), revert to the last-good definition, then backfill corrected history.
Dual-publish during transition: expose old and new metric versions side by side with a dated changelog entry instead of silently mutating one series.

Scoring Rubric #

SQL and metric correctness (35%)
modeling and semantic coherence (25%)
risk/incident strategy (25%)
communication quality (15%)

Warning

Senior interviews usually fail on weak risk handling, not basic SQL syntax.