Open the worked query only after you can describe the base relation and join
cardinality in words.
04 · Advanced35 min
Rolling seven-day active entities
Situation: Product analytics wants a complete July activity series, including days with no serving traffic.
Question: For every day in July 2026, calculate the trailing seven-day distinct entity count across production predictions. Keep zero-volume days.
Read the data at the right grain
Rolling distinct users cannot be computed by summing daily distinct counts because one entity can appear on several days. Join every report day to its raw seven-day event range.
Tables: prediction_events → deployments
Construct the query
- Generate all 31 July dates before touching facts.
- Pre-filter production events to the smallest partition-prunable date range.
- LEFT JOIN each date to [day - 6 days, day + 1 day).
- COUNT DISTINCT entity_key at day grain.
Show the runnable worked query
WITH days AS (
SELECT day::date
FROM generate_series(date '2026-07-01', date '2026-07-31', interval '1 day') AS g(day)
), production_events AS (
SELECT pe.predicted_at, pe.entity_key
FROM prediction_events pe
JOIN deployments d USING (deployment_id)
WHERE d.environment = 'production'
AND pe.predicted_at >= timestamptz '2026-06-25 00:00+00'
AND pe.predicted_at < timestamptz '2026-08-01 00:00+00'
)
SELECT days.day,
count(DISTINCT pe.entity_key) AS trailing_7d_entities
FROM days
LEFT JOIN production_events pe
ON pe.predicted_at >= days.day::timestamptz - interval '6 days'
AND pe.predicted_at < days.day::timestamptz + interval '1 day'
GROUP BY days.day
ORDER BY days.day;
How to read the result
Exactly 31 rows are returned, including a zero for a date with no matching traffic.
What makes this a senior/staff answer
Large-scale rolling distinct normally uses sketches or maintained entity-day state; the exact range join is correctness-first, not the cheapest possible design.
Interview traps and follow-ups
- Generate the date spine first
- Define whether the current day is included
- Why can’t daily distincts be summed?
- How would you incrementally maintain this metric?
05 · Advanced45 min
Detect release regressions
Situation: A rollout gate must identify model versions that degraded serving performance.
Question: For each production deployment, compare its first 72 complete hours of p95 latency and error rate with the preceding deployment for the same project. Flag either metric when it regresses by more than 20%.
Read the data at the right grain
Build a release dimension with previous_deployment_id, independently aggregate a fixed observation window for every release, then self-join current and baseline metrics.
Tables: projects → deployments → prediction_events
Construct the query
- LAG deployment_id within each project's production history.
- Define 72 complete hours with half-open timestamp bounds.
- Aggregate p95 latency and error rate once per deployment.
- Join current metrics to the preceding deployment and calculate guarded relative deltas.
Show the runnable worked query
WITH ordered_deployments AS (
SELECT d.*,
lag(d.deployment_id) OVER (
PARTITION BY d.project_id
ORDER BY d.deployed_at, d.deployment_id
) AS previous_deployment_id
FROM deployments d
WHERE d.environment = 'production'
), release_metrics AS (
SELECT d.deployment_id,
count(pe.prediction_id) AS request_count,
percentile_cont(0.95) WITHIN GROUP (ORDER BY pe.latency_ms) AS p95_latency_ms,
avg((pe.http_status >= 500)::int) AS error_rate
FROM ordered_deployments d
LEFT JOIN prediction_events pe
ON pe.deployment_id = d.deployment_id
AND pe.predicted_at >= date_trunc('hour', d.deployed_at) + interval '1 hour'
AND pe.predicted_at < date_trunc('hour', d.deployed_at) + interval '73 hours'
GROUP BY d.deployment_id
)
SELECT p.project_name,
d.deployment_id,
d.previous_deployment_id,
cur.request_count,
cur.p95_latency_ms,
prev.p95_latency_ms AS previous_p95_latency_ms,
cur.error_rate,
prev.error_rate AS previous_error_rate,
cur.p95_latency_ms / nullif(prev.p95_latency_ms, 0) - 1 AS latency_delta,
cur.error_rate / nullif(prev.error_rate, 0) - 1 AS error_delta,
cur.p95_latency_ms > prev.p95_latency_ms * 1.20 AS latency_regressed,
cur.error_rate > prev.error_rate * 1.20 AS errors_regressed
FROM ordered_deployments d
JOIN projects p USING (project_id)
JOIN release_metrics cur USING (deployment_id)
LEFT JOIN release_metrics prev
ON prev.deployment_id = d.previous_deployment_id
ORDER BY p.project_name, d.deployed_at;
How to read the result
Each release shows its own and its predecessor's metrics plus >20% regression flags.
What makes this a senior/staff answer
Overlapping rollouts make raw deployment comparisons traffic-selection biased. Require sample-size gates and consider endpoint-hour traffic weighting or controlled canary cohorts.
Interview traps and follow-ups
- Overlapping rollouts can share traffic
- A zero baseline needs explicit handling
- How would traffic weighting change the answer?
- What minimum sample size would you require?
06 · Advanced35 min
Quality with delayed labels
Situation: Offline labels arrive late, so a quality dashboard must expose how representative its metric is.
Question: Calculate weekly MAE by project and device family. Report label coverage and return NULL MAE when coverage is below 60%. Explain why filtering unlabeled events in WHERE would be misleading.
Read the data at the right grain
Coverage and error use different populations. Count all requests for coverage, but calculate MAE only where a label exists.
Tables: projects → deployments → prediction_events
Construct the query
- Aggregate production events at project-week-device grain.
- Count all rows and non-null ground_truth separately.
- Calculate raw MAE with FILTER rather than removing unlabeled rows in WHERE.
- Use CASE to suppress MAE when labeled/request coverage is under 60%.
Show the runnable worked query
WITH weekly AS (
SELECT d.project_id,
date_trunc('week', pe.predicted_at) AS week_start,
pe.device_family,
count(*) AS request_count,
count(pe.ground_truth) AS labeled_count,
avg(abs(pe.prediction - pe.ground_truth))
FILTER (WHERE pe.ground_truth IS NOT NULL) AS raw_mae
FROM prediction_events pe
JOIN deployments d USING (deployment_id)
WHERE d.environment = 'production'
GROUP BY d.project_id, date_trunc('week', pe.predicted_at), pe.device_family
)
SELECT p.project_name,
w.week_start,
w.device_family,
w.request_count,
w.labeled_count,
w.labeled_count::numeric / nullif(w.request_count, 0) AS label_coverage,
CASE
WHEN w.labeled_count::numeric / nullif(w.request_count, 0) >= 0.60
THEN w.raw_mae
END AS guarded_mae
FROM weekly w
JOIN projects p USING (project_id)
ORDER BY w.week_start, p.project_name, w.device_family;
How to read the result
The result exposes request count, label count, coverage, and a nullable guarded quality metric.
What makes this a senior/staff answer
A 60% threshold does not correct non-random missing labels. Track label arrival delay and re-materialize recent windows as late labels arrive.
Interview traps and follow-ups
- Coverage denominator includes unlabeled events
- HTTP failures may have NULL predictions
- How would label delay bias recent weeks?
- How would you add confidence intervals?
07 · Advanced40 min
Consecutive pipeline failures
Situation: Single failures are noisy; sustained pipeline failure streaks require escalation.
Question: Return every streak of at least three consecutive failed runs for a pipeline. A non-failure breaks the streak; order runs by scheduled time and a deterministic tie-breaker.
Read the data at the right grain
A running count of non-failures creates an island identifier: every success/cancel changes the identifier, while adjacent failures keep the same value.
Tables: pipeline_definitions → pipeline_runs
Construct the query
- Order runs within a pipeline by scheduled_for and pipeline_run_id.
- Cumulatively sum status <> failed to create failure_island.
- Filter failures only after assigning islands.
- Group each island and retain groups with at least three rows.
Show the runnable worked query
WITH ordered AS (
SELECT pr.*,
sum((pr.status <> 'failed')::int) OVER (
PARTITION BY pr.pipeline_id
ORDER BY pr.scheduled_for, pr.pipeline_run_id
) AS failure_island
FROM pipeline_runs pr
), streaks AS (
SELECT pipeline_id,
failure_island,
min(scheduled_for) AS streak_start,
max(scheduled_for) AS streak_end,
count(*) AS failed_runs
FROM ordered
WHERE status = 'failed'
GROUP BY pipeline_id, failure_island
HAVING count(*) >= 3
)
SELECT pd.pipeline_name,
s.streak_start,
s.streak_end,
s.failed_runs,
s.streak_end - s.streak_start AS streak_duration
FROM streaks s
JOIN pipeline_definitions pd USING (pipeline_id)
ORDER BY s.streak_start, pd.pipeline_name;
How to read the result
One row per qualifying failure streak includes its boundaries, count, and duration.
What makes this a senior/staff answer
Define whether cancelled/running rows break a streak and whether late status corrections can rewrite an already paged incident.
Interview traps and follow-ups
- Do not group all failures together
- Tied schedules require pipeline_run_id ordering
- Should cancelled runs break a streak?
- How would late run-state updates affect this?
08 · Advanced45 min
Pareto-optimal training runs
Situation: Model owners want candidates that balance quality and serving latency without choosing arbitrary weights.
Question: For each model, select succeeded runs on the Pareto frontier: no other run for that model has both test AUC at least as high and test latency at most as low, with at least one strict improvement.
Read the data at the right grain
A run is Pareto-optimal when no peer dominates it. Dominance uses weak comparisons on both metrics and requires at least one strict improvement.
Tables: models → experiments → training_runs → run_metrics
Construct the query
- Pivot test AUC and test latency into one candidate row per successful run.
- Discard candidates missing either objective.
- Use NOT EXISTS to search for a better-or-equal peer in the same model.
- Keep exact ties because neither tied row strictly improves the other.
Show the runnable worked query
WITH candidates AS (
SELECT m.model_id,
m.model_name,
r.run_id,
max(rm.metric_value) FILTER (
WHERE rm.metric_name = 'auc' AND rm.split = 'test'
) AS test_auc,
max(rm.metric_value) FILTER (
WHERE rm.metric_name = 'latency_ms' AND rm.split = 'test'
) AS test_latency_ms
FROM training_runs r
JOIN experiments e USING (experiment_id)
JOIN models m USING (model_id)
JOIN run_metrics rm USING (run_id)
WHERE r.status = 'succeeded'
GROUP BY m.model_id, m.model_name, r.run_id
), frontier AS (
SELECT a.*
FROM candidates a
WHERE a.test_auc IS NOT NULL
AND a.test_latency_ms IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM candidates b
WHERE b.model_id = a.model_id
AND b.test_auc >= a.test_auc
AND b.test_latency_ms <= a.test_latency_ms
AND (b.test_auc > a.test_auc OR b.test_latency_ms < a.test_latency_ms)
)
)
SELECT *
FROM frontier
ORDER BY model_name, test_auc DESC, test_latency_ms, run_id;
How to read the result
Every model can return several frontier runs; this is expected and avoids inventing arbitrary objective weights.
What makes this a senior/staff answer
Add cost or memory as more dominance dimensions carefully—the frontier can grow quickly. Persist metric definitions and directionality.
Interview traps and follow-ups
- Pivot metrics without multiplying runs
- Use weak comparisons plus one strict comparison
- How would you add cost as a third objective?
- How should missing metrics behave?
17 · Advanced40 min
Concurrent rollout validation
Situation: Two model versions can serve one endpoint during a canary, but their traffic must never exceed 100%.
Question: Reconstruct traffic allocations over time for every project and identify intervals where active production deployments sum above 100% or below 100%. Return interval boundaries and the involved deployments.
Read the data at the right grain
Effective-dated state is constant between change points. Build those intervals, reconstruct each active deployment's latest allocation at the interval start, then sum.
Tables: deployments → deployment_traffic
Construct the query
- Collect distinct production traffic effective_at values by project.
- LEAD each timestamp to form half-open intervals.
- Join deployments alive at each interval start and lateral-select their latest traffic row.
- Group and retain totals not equal to 100%.
Show the runnable worked query
WITH change_points AS (
SELECT DISTINCT d.project_id, dt.effective_at AS interval_start
FROM deployment_traffic dt
JOIN deployments d USING (deployment_id)
WHERE d.environment = 'production'
), intervals AS (
SELECT project_id,
interval_start,
lead(interval_start) OVER (
PARTITION BY project_id ORDER BY interval_start
) AS interval_end
FROM change_points
), allocations AS (
SELECT i.project_id,
i.interval_start,
i.interval_end,
d.deployment_id,
latest.traffic_percent
FROM intervals i
JOIN deployments d
ON d.project_id = i.project_id
AND d.environment = 'production'
AND d.deployed_at <= i.interval_start
AND (d.retired_at IS NULL OR d.retired_at > i.interval_start)
LEFT JOIN LATERAL (
SELECT dt.traffic_percent
FROM deployment_traffic dt
WHERE dt.deployment_id = d.deployment_id
AND dt.effective_at <= i.interval_start
ORDER BY dt.effective_at DESC
LIMIT 1
) AS latest ON true
)
SELECT p.project_name,
a.interval_start,
a.interval_end,
sum(coalesce(a.traffic_percent, 0)) AS total_traffic_percent,
array_agg(a.deployment_id ORDER BY a.deployment_id)
FILTER (WHERE a.traffic_percent IS NOT NULL) AS deployments
FROM allocations a
JOIN projects p USING (project_id)
GROUP BY p.project_name, a.interval_start, a.interval_end
HAVING sum(coalesce(a.traffic_percent, 0)) <> 100
ORDER BY a.interval_start, p.project_name;
How to read the result
Every returned interval is over- or under-allocated and includes the deployment IDs contributing traffic.
What makes this a senior/staff answer
A simple CHECK cannot enforce a cross-row total. Serialize rollout changes per endpoint, validate inside the same transaction, or model allocation as one versioned document.
Interview traps and follow-ups
- Traffic records are effective-dated
- Check totals between change points, not only at record timestamps
- Can a SQL constraint enforce cross-row totals?
- How would you serialize rollout updates?
18 · Advanced40 min
Pipeline retry correctness
Situation: Operations reports double-count jobs because retries are treated as independent scheduled work.
Question: Group pipeline runs into logical retry chains using retry_of. For each chain, report total attempts, final status, time to eventual success, and records published exactly once.
Read the data at the right grain
retry_of forms a graph whose intended shape is a chain. Recursive traversal assigns every descendant to a root logical run.
Tables: pipeline_runs → pipeline_task_runs
Construct the query
- Anchor on rows with no retry_of.
- Recursively join children whose retry_of points to the current attempt.
- Rank attempts descending to identify terminal state.
- Aggregate attempts, elapsed time, and only the terminal successful output.
Show the runnable worked query
WITH RECURSIVE chains AS (
SELECT pr.pipeline_run_id AS root_run_id,
pr.pipeline_run_id,
pr.pipeline_id,
pr.status,
pr.scheduled_for,
pr.finished_at,
pr.records_out,
1 AS attempt_no
FROM pipeline_runs pr
WHERE pr.retry_of IS NULL
UNION ALL
SELECT c.root_run_id,
child.pipeline_run_id,
child.pipeline_id,
child.status,
child.scheduled_for,
child.finished_at,
child.records_out,
c.attempt_no + 1
FROM chains c
JOIN pipeline_runs child ON child.retry_of = c.pipeline_run_id
), ranked AS (
SELECT c.*,
row_number() OVER (
PARTITION BY root_run_id
ORDER BY attempt_no DESC, pipeline_run_id DESC
) AS terminal_rank
FROM chains c
)
SELECT pd.pipeline_name,
root_run_id,
count(*) AS attempts,
max(status) FILTER (WHERE terminal_rank = 1) AS final_status,
max(finished_at) FILTER (WHERE status = 'succeeded')
- min(scheduled_for) AS time_to_success,
max(records_out) FILTER (
WHERE status = 'succeeded' AND terminal_rank = 1
) AS records_published_once
FROM ranked
JOIN pipeline_definitions pd USING (pipeline_id)
GROUP BY pd.pipeline_name, root_run_id
ORDER BY root_run_id;
How to read the result
One row represents one logical pipeline execution, not one physical attempt.
What makes this a senior/staff answer
Enforce same-pipeline retry links, prevent cycles and branching, and store an explicit logical_run_id. Exactly-once publication requires an idempotency key at the sink.
Interview traps and follow-ups
- The seed has nullable retry links; design for future chains
- Do not sum duplicate published outputs blindly
- What invariant should retry_of enforce?
- How would you detect branching retry chains?
19 · Advanced40 min
Attribute pipeline compute cost
Situation: Platform finance needs project-level compute attribution without charging failed retries twice.
Question: Calculate weekly CPU-hours and peak-memory-hours by project, pipeline, and task. Separate succeeded work, failed work, and retry overhead; rank the top cost-growth projects week over week.
Read the data at the right grain
Normalize every task attempt to resource units first, then roll up at one declared weekly grain before calculating growth.
Tables: projects → pipeline_definitions → pipeline_runs → pipeline_task_runs
Construct the query
- Join task attempts to pipeline/project and week.
- Treat cpu_seconds as cumulative CPU and divide by 3600.
- Estimate memory GB-hours as peak MB × wall time, clearly labeling the assumption.
- Separate success/failure/retry with FILTER, then LAG totals for growth.
Show the runnable worked query
WITH task_cost AS (
SELECT pd.project_id,
pd.pipeline_name,
ptr.task_name,
date_trunc('week', pr.scheduled_for) AS week_start,
pr.status AS run_status,
pr.retry_of IS NOT NULL AS is_retry,
coalesce(ptr.cpu_seconds, 0) / 3600.0 AS cpu_hours,
coalesce(ptr.memory_peak_mb, 0)
* greatest(extract(epoch FROM ptr.finished_at - ptr.started_at), 0)
/ 3600.0 / 1024.0 AS estimated_memory_gb_hours
FROM pipeline_task_runs ptr
JOIN pipeline_runs pr USING (pipeline_run_id)
JOIN pipeline_definitions pd USING (pipeline_id)
), weekly AS (
SELECT project_id,
pipeline_name,
task_name,
week_start,
sum(cpu_hours) FILTER (WHERE run_status = 'succeeded' AND NOT is_retry) AS succeeded_cpu_hours,
sum(cpu_hours) FILTER (WHERE run_status = 'failed' AND NOT is_retry) AS failed_cpu_hours,
sum(cpu_hours) FILTER (WHERE is_retry) AS retry_cpu_hours,
sum(estimated_memory_gb_hours) AS estimated_memory_gb_hours,
sum(cpu_hours) AS total_cpu_hours
FROM task_cost
GROUP BY project_id, pipeline_name, task_name, week_start
), growth AS (
SELECT w.*,
w.total_cpu_hours / nullif(lag(w.total_cpu_hours) OVER (
PARTITION BY w.project_id, w.pipeline_name, w.task_name
ORDER BY w.week_start
), 0) - 1 AS week_over_week_growth
FROM weekly w
)
SELECT p.project_name, g.*,
dense_rank() OVER (
PARTITION BY g.week_start
ORDER BY g.week_over_week_growth DESC NULLS LAST
) AS growth_rank
FROM growth g
JOIN projects p USING (project_id)
ORDER BY g.week_start, growth_rank, p.project_name;
-- memory_peak_mb is a gauge, not usage. The query explicitly estimates GB-hours
-- as peak memory times wall-clock duration; billing data would be preferable.
How to read the result
Weekly task rows expose cost categories, estimated memory usage, growth, and a within-week rank.
What makes this a senior/staff answer
Peak memory is not billed memory usage. Join provider prices, machine type, region, accelerator time, and shared-cluster allocation before calling this financial cost.
Interview traps and follow-ups
- Peak MB is not automatically MB-hours
- Task attempts and pipeline runs have different grains
- What additional pricing dimensions are missing?
- How would you allocate shared clusters?
23 · Advanced45 min
Serving capacity recommendation
Situation: SRE wants replica settings based on observed peak load and latency rather than static guesses.
Question: For each active production endpoint, calculate peak five-minute requests, p95 latency during that peak, current min/max replicas, and a recommended minimum replica count with 30% headroom.
Read the data at the right grain
Capacity starts with a load window. Aggregate five-minute buckets, rank the peak per deployment, then apply an explicitly named throughput assumption and headroom.
Tables: deployments → prediction_events
Construct the query
- Restrict to current active production deployments.
- DATE_BIN events into five-minute windows and compute requests plus p95 latency.
- ROW_NUMBER each deployment's windows by request count.
- Apply 30% headroom to an illustrative requests-per-replica capacity.
Show the runnable worked query
WITH five_minute AS (
SELECT d.deployment_id,
d.endpoint_name,
d.min_replicas,
d.max_replicas,
date_bin(interval '5 minutes', pe.predicted_at,
timestamptz '2026-01-01 00:00+00') AS window_start,
count(*) AS requests,
percentile_cont(0.95) WITHIN GROUP (ORDER BY pe.latency_ms) AS p95_latency_ms
FROM deployments d
JOIN prediction_events pe USING (deployment_id)
WHERE d.environment = 'production'
AND d.status = 'active'
GROUP BY d.deployment_id, d.endpoint_name, d.min_replicas, d.max_replicas,
date_bin(interval '5 minutes', pe.predicted_at,
timestamptz '2026-01-01 00:00+00')
), ranked AS (
SELECT *,
row_number() OVER (
PARTITION BY deployment_id
ORDER BY requests DESC, window_start DESC
) AS peak_rank
FROM five_minute
)
SELECT deployment_id,
endpoint_name,
window_start AS peak_window,
requests AS peak_5m_requests,
p95_latency_ms,
min_replicas,
max_replicas,
ceil(requests * 1.30 / 100.0) AS illustrative_recommended_min
FROM ranked
WHERE peak_rank = 1
ORDER BY endpoint_name;
-- The 100 requests/replica/5m assumption is illustrative. A defensible model
-- needs per-replica concurrency, utilization, throttling, and cold-start data.
How to read the result
Each active endpoint gets one observed peak window, latency, current bounds, and an illustrative minimum.
What makes this a senior/staff answer
The database lacks per-replica throughput, utilization, concurrency, throttling, and cold starts. Do not ship the illustrative 100-request assumption as autoscaling policy.
Interview traps and follow-ups
- The schema lacks per-replica throughput
- Multiple active deployments may share an endpoint
- What telemetry is missing for a defensible recommendation?
- How would cold starts change headroom?
27 · Advanced35 min
Feature freshness SLO breaches
Situation: Online features may be technically successful but arrive too far apart.
Question: Find gaps between successful materializations that exceed each feature's freshness_slo and report the overage.
Read the data at the right grain
Freshness is a gap between consecutive successful completions compared with a feature-specific interval.
Tables: feature_definitions → feature_materializations
Construct the query
- Keep only completed successful materializations.
- LAG completion per feature.
- Subtract timestamps to obtain an interval.
- Filter gaps greater than freshness_slo and calculate overage.
Show the runnable worked query
WITH successful AS (
SELECT f.feature_id,
f.feature_name,
f.freshness_slo,
fm.materialization_id,
fm.completed_at,
lag(fm.completed_at) OVER (
PARTITION BY f.feature_id
ORDER BY fm.completed_at, fm.materialization_id
) AS previous_completed_at
FROM feature_definitions f
JOIN feature_materializations fm USING (feature_id)
WHERE fm.status = 'succeeded'
AND fm.completed_at IS NOT NULL
)
SELECT *,
completed_at - previous_completed_at AS observed_gap,
completed_at - previous_completed_at - freshness_slo AS slo_overage
FROM successful
WHERE completed_at - previous_completed_at > freshness_slo
ORDER BY slo_overage DESC, feature_name;
How to read the result
Every row is a historical SLO breach boundary rather than an individual failed attempt.
What makes this a senior/staff answer
Also compare now() with the latest completion for current staleness and distinguish source watermark freshness from job completion freshness.
Interview traps and follow-ups
- Failed runs do not reset successful freshness
- The first success has no prior boundary
- How do you detect a currently stale feature?
- Which event time defines freshness?
28 · Advanced35 min
Deterministic experiment champion
Situation: Automation needs exactly one reproducible promotion candidate per model.
Question: Choose one succeeded run per model by validation AUC, test latency, then run_id. Require both metrics.
Read the data at the right grain
Pivot metrics into candidate rows, remove incomplete candidates, then rank within model using the complete business ordering.
Tables: training_runs → run_metrics → experiments → models
Construct the query
- Aggregate one row per successful run.
- FILTER the two named split metrics.
- Drop incomplete candidates.
- ROW_NUMBER by AUC desc, latency asc, run_id.
Show the runnable worked query
WITH candidate_metrics AS (
SELECT m.model_id,
m.model_name,
r.run_id,
max(rm.metric_value) FILTER (
WHERE rm.metric_name = 'auc' AND rm.split = 'validation'
) AS validation_auc,
max(rm.metric_value) FILTER (
WHERE rm.metric_name = 'latency_ms' AND rm.split = 'test'
) AS test_latency_ms
FROM training_runs r
JOIN experiments e USING (experiment_id)
JOIN models m USING (model_id)
JOIN run_metrics rm USING (run_id)
WHERE r.status = 'succeeded'
GROUP BY m.model_id, m.model_name, r.run_id
), ranked AS (
SELECT *,
row_number() OVER (
PARTITION BY model_id
ORDER BY validation_auc DESC, test_latency_ms, run_id
) AS champion_rank
FROM candidate_metrics
WHERE validation_auc IS NOT NULL AND test_latency_ms IS NOT NULL
)
SELECT model_name, run_id, validation_auc, test_latency_ms
FROM ranked
WHERE champion_rank = 1
ORDER BY model_name;
How to read the result
Exactly one deterministic champion is selected for every model with eligible runs.
What makes this a senior/staff answer
Selection on a test metric can leak evaluation information. Encode hard safety constraints and metric-definition versions before ranking.
Interview traps and follow-ups
- Do not compare metrics across models
- Missing metrics are ineligible
- Is test latency a safe selection metric?
- How would hard constraints precede ranking?
29 · Advanced35 min
Incident recovery with censoring
Situation: A naïve MTTR dashboard makes teams with unresolved incidents look artificially fast.
Question: Report project incident count, resolved MTTR, open backlog, and oldest open age as of 2026-08-17.
Read the data at the right grain
Resolved duration and open exposure are separate measures. LEFT JOIN from projects so a zero-incident project remains visible.
Tables: projects → incidents
Construct the query
- Anchor the explicit as-of time.
- Restrict joined incidents to those already detected.
- FILTER MTTR to resolved rows only.
- Separately count and age incidents unresolved at the as-of instant.
Show the runnable worked query
SELECT p.project_name,
count(i.incident_id) AS incident_count,
count(i.incident_id) FILTER (WHERE i.resolved_at IS NOT NULL) AS resolved_count,
avg(i.resolved_at - i.detected_at)
FILTER (WHERE i.resolved_at IS NOT NULL) AS resolved_mttr,
count(i.incident_id) FILTER (
WHERE i.resolved_at IS NULL
OR i.resolved_at > timestamptz '2026-08-17 00:00+00'
) AS open_as_of_count,
max(timestamptz '2026-08-17 00:00+00' - i.detected_at) FILTER (
WHERE i.detected_at <= timestamptz '2026-08-17 00:00+00'
AND (i.resolved_at IS NULL OR i.resolved_at > timestamptz '2026-08-17 00:00+00')
) AS oldest_open_age
FROM projects p
LEFT JOIN incidents i
ON i.project_id = p.project_id
AND i.detected_at <= timestamptz '2026-08-17 00:00+00'
GROUP BY p.project_name
ORDER BY incident_count DESC, p.project_name;
How to read the result
The table refuses to turn unresolved work into favorable zero-duration observations.
What makes this a senior/staff answer
Use percentile and survival curves for skewed recovery, and version incident state so historical as-of reporting is auditable.
Interview traps and follow-ups
- Open incidents are censored, not zero duration
- Exclude incidents detected after the as-of time
- Would median be safer?
- How would survival analysis help?