01 · Coreanalytics20 minutes
Deployment cadence by project
Scenario: Platform leadership wants to understand how frequently production models change.
Your task: For every project, show each production deployment and the hours since its preceding production deployment. Retain the first deployment with a NULL interval.
Tables in play
projectsdeployments
Required output
- One row per production deployment
- Project name, deployment ID, deployed timestamp, and hours since previous
- Deterministic ordering when timestamps tie
Edges to watch and interviewer follow-ups
Edges to watch
- Partition the window by project
- Do not drop the first deployment
Follow-ups
- How would you calculate median cadence by team?
- What index supports this query?
02 · Coretraining30 minutes
Weekly training health
Scenario: The training platform needs a weekly reliability scorecard for each ML product.
Your task: By project and calendar week, calculate run count, success rate, failure rate, p50 queue delay, and p95 runtime. Return only project-weeks with at least 20 runs.
Tables in play
projectsmodelsexperimentstraining_runs
Required output
- Rates between 0 and 1
- Queue and runtime expressed in seconds
- One row per project-week
Edges to watch and interviewer follow-ups
Edges to watch
- Cancelled runs belong in the denominator
- NULL timestamps must not break percentiles
Follow-ups
- Would you use mean or percentile for runtime?
- How would retries alter the metric?
03 · Coreserving20 minutes
Effective rollout traffic
Scenario: An incident commander needs the exact traffic allocation that was active at a historical instant.
Your task: Find the effective traffic percentage for every active deployment as of 2026-07-15 12:00 UTC. Do not use a correlated scalar subquery.
Tables in play
deploymentsdeployment_traffic
Required output
- Deployment, endpoint, effective timestamp, and traffic percentage
- At most one row per deployment
Edges to watch and interviewer follow-ups
Edges to watch
- Exclude future traffic changes
- Some deployments may have no earlier traffic record
Follow-ups
- Compare DISTINCT ON with ROW_NUMBER
- How would you model rollout end times?
04 · Advancedanalytics35 minutes
Rolling seven-day active entities
Scenario: Product analytics wants a complete July activity series, including days with no serving traffic.
Your task: For every day in July 2026, calculate the trailing seven-day distinct entity count across production predictions. Keep zero-volume days.
Tables in play
prediction_eventsdeployments
Required output
- Exactly 31 rows
- A half-open seven-day window per date
- Distinct entities—not summed daily distinct counts
Edges to watch and interviewer follow-ups
Edges to watch
- Generate the date spine first
- Define whether the current day is included
Follow-ups
- Why can’t daily distincts be summed?
- How would you incrementally maintain this metric?
05 · Advancedserving45 minutes
Detect release regressions
Scenario: A rollout gate must identify model versions that degraded serving performance.
Your task: 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%.
Tables in play
projectsdeploymentsprediction_events
Required output
- Current and baseline metrics
- Relative deltas
- Latency and error regression flags
Edges to watch and interviewer follow-ups
Edges to watch
- Overlapping rollouts can share traffic
- A zero baseline needs explicit handling
Follow-ups
- How would traffic weighting change the answer?
- What minimum sample size would you require?
06 · Advancedserving35 minutes
Quality with delayed labels
Scenario: Offline labels arrive late, so a quality dashboard must expose how representative its metric is.
Your task: 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.
Tables in play
projectsdeploymentsprediction_events
Required output
- Request count, labeled count, coverage, and guarded MAE
- One row per project-week-device
Edges to watch and interviewer follow-ups
Edges to watch
- Coverage denominator includes unlabeled events
- HTTP failures may have NULL predictions
Follow-ups
- How would label delay bias recent weeks?
- How would you add confidence intervals?
07 · Advancedpipelines40 minutes
Consecutive pipeline failures
Scenario: Single failures are noisy; sustained pipeline failure streaks require escalation.
Your task: 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.
Tables in play
pipeline_definitionspipeline_runs
Required output
- Pipeline, streak start/end, run count, and duration
- One row per qualifying streak
Edges to watch and interviewer follow-ups
Edges to watch
- Do not group all failures together
- Tied schedules require pipeline_run_id ordering
Follow-ups
- Should cancelled runs break a streak?
- How would late run-state updates affect this?
08 · Advancedtraining45 minutes
Pareto-optimal training runs
Scenario: Model owners want candidates that balance quality and serving latency without choosing arbitrary weights.
Your task: 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.
Tables in play
modelsexperimentstraining_runsrun_metrics
Required output
- Model, run, test AUC, and test latency
- All non-dominated runs, including exact ties
Edges to watch and interviewer follow-ups
Edges to watch
- Pivot metrics without multiplying runs
- Use weak comparisons plus one strict comparison
Follow-ups
- How would you add cost as a third objective?
- How should missing metrics behave?
09 · Staffreliability55 minutes
Attribute incidents to releases
Scenario: Engineering leadership wants release-level change-failure metrics without claiming every incident was caused by a deployment.
Your task: Attribute each incident to the closest preceding deployment in the same project within 48 hours. Then report attributed incident rate and median time-to-detect by deployer while preserving unattributed incidents for audit.
Tables in play
incidentsdeploymentsengineers
Required output
- An auditable incident-to-deployment mapping
- A deployer-level aggregate
- Unattributed count
Edges to watch and interviewer follow-ups
Edges to watch
- Temporal proximity is attribution, not causation
- Use the total deployment count as the rate denominator
Follow-ups
- How would you validate attribution quality?
- What if several services deploy together?
10 · Stafftraining60 minutes
Point-in-time feature reconstruction
Scenario: A reproducibility audit needs the feature state available when each artifact was created.
Your task: For each model version and each non-deprecated feature in its project, select the latest successful materialization completed on or before model creation. Surface features with no eligible materialization.
Tables in play
modelsmodel_versionsfeature_definitionsfeature_materializations
Required output
- One row per model-version-feature
- Chosen materialization and completion time
- Explicit missing flag
Edges to watch and interviewer follow-ups
Edges to watch
- A current latest value leaks future data
- LEFT/LATERAL semantics preserve missing features
Follow-ups
- What additional lineage key is missing?
- How would you make reconstruction immutable?
11 · Staffgovernance50 minutes
Audit deployment governance
Scenario: Compliance asks whether every production artifact had a valid approval before release.
Your task: Find production deployments for which no approved production governance decision existed before deployed_at. Then identify why the current approval schema cannot prove which artifact digest was reviewed and propose corrective DDL.
Tables in play
deploymentsmodel_versionsgovernance_approvals
Required output
- Violating deployment list
- Violation reason
- Migration-safe DDL proposal
Edges to watch and interviewer follow-ups
Edges to watch
- A later approval does not make an earlier deployment compliant
- Model version identity is weaker than digest attestation
Follow-ups
- Would an approval be mutable?
- How would emergency break-glass releases work?
12 · Staffreliability60 minutes
Measure drift impact
Scenario: Hundreds of drift alerts fire, but on-call needs the few correlated with customer harm.
Your task: For drift windows whose score exceeds threshold, compare error rate and MAE inside the window against the immediately preceding equal-length window. Rank features by estimated impact without double-counting prediction traffic when multiple features drift together.
Tables in play
drift_signalsprediction_eventsdeployments
Required output
- Baseline and drift-window metrics
- Metric deltas
- A documented non-duplicating impact score
Edges to watch and interviewer follow-ups
Edges to watch
- Joining signals directly to events multiplies traffic
- MAE coverage differs between windows
Follow-ups
- Can correlation establish root cause?
- How would you control alert multiplicity?
13 · Staffreliability60 minutes
Multi-window SLO burn rate
Scenario: Serving owns a 99.5% success SLO and needs a low-noise page based on fast and slow burn.
Your task: Produce one row per production endpoint-hour with one-hour and six-hour error-budget burn rates. Flag only hours where both exceed 14.4x and 6x respectively.
Tables in play
deploymentsprediction_events
Required output
- Hourly requests/errors
- Both burn rates
- Page flag and sample-size context
Edges to watch and interviewer follow-ups
Edges to watch
- Allowed error rate is 0.5%
- Sparse hours and zero traffic need an explicit policy
Follow-ups
- Why use two windows?
- How would late events revise a page decision?
14 · Staffgovernance55 minutes
Quality failure blast radius
Scenario: A failed dataset check requires identifying every potentially affected production endpoint.
Your task: Trace failed quality-check results through dataset versions, model versions, and production deployments. Return the affected endpoint blast radius and propose a generic lineage-edge table that supports arbitrary depth.
Tables in play
quality_check_resultsdataset_versionsmodel_versionsdeployments
Required output
- Failed check to endpoint paths
- Distinct affected endpoints
- Cycle-safe recursive lineage design
Edges to watch and interviewer follow-ups
Edges to watch
- Current schema supports a direct path, not arbitrary recursion
- Deduplicate endpoints after preserving paths
Follow-ups
- How would you handle cycles?
- Where would feature lineage enter the graph?
15 · Staffserving50 minutes
Repair a pathological query plan
Scenario: A dashboard query scans excessive partitions and returns inflated request counts.
Your task: Repair the intentionally poor query in database/exercises.sql: make the timestamp predicate sargable, eliminate drift-signal row multiplication, and propose the smallest useful index. Compare plans with EXPLAIN (ANALYZE, BUFFERS).
Tables in play
projectsdeploymentsprediction_eventsdrift_signals
Required output
- Corrected query
- Before/after plan observations
- Index DDL with write-cost justification
Edges to watch and interviewer follow-ups
Edges to watch
- Functions on predicted_at defeat pruning
- DISTINCT after a bad join may hide, not fix, incorrectness
Follow-ups
- When is a sequential scan correct?
- Why might a new index be rejected?
16 · Staffpipelines60 minutes
Design an idempotent health backfill
Scenario: A metric bug requires recomputing a week of serving health while dashboards remain online.
Your task: Recompute daily deployment health for 2026-07-01 through 2026-07-07 so retries are safe and readers never observe partial results. Defend a table, materialized-view refresh, or partition-swap design.
Tables in play
prediction_eventsdaily_deployment_health
Required output
- Runnable transaction or staging flow
- Idempotency key
- Reader-consistency explanation
Edges to watch and interviewer follow-ups
Edges to watch
- The existing materialized view cannot be partially refreshed
- DELETE then INSERT is unsafe without one transaction
Follow-ups
- How do you bound locks?
- How do you verify before publishing?
17 · Advancedserving40 minutes
Concurrent rollout validation
Scenario: Two model versions can serve one endpoint during a canary, but their traffic must never exceed 100%.
Your task: 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.
Tables in play
deploymentsdeployment_traffic
Required output
- Change-point intervals
- Total allocation
- Over/under-allocation classification
Edges to watch and interviewer follow-ups
Edges to watch
- Traffic records are effective-dated
- Check totals between change points, not only at record timestamps
Follow-ups
- Can a SQL constraint enforce cross-row totals?
- How would you serialize rollout updates?
18 · Advancedpipelines40 minutes
Pipeline retry correctness
Scenario: Operations reports double-count jobs because retries are treated as independent scheduled work.
Your task: 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.
Tables in play
pipeline_runspipeline_task_runs
Required output
- One row per logical run chain
- Root and terminal run IDs
- Attempt and output totals
Edges to watch and interviewer follow-ups
Edges to watch
- The seed has nullable retry links; design for future chains
- Do not sum duplicate published outputs blindly
Follow-ups
- What invariant should retry_of enforce?
- How would you detect branching retry chains?
19 · Advancedpipelines40 minutes
Attribute pipeline compute cost
Scenario: Platform finance needs project-level compute attribution without charging failed retries twice.
Your task: 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.
Tables in play
projectspipeline_definitionspipeline_runspipeline_task_runs
Required output
- Weekly cost facts at a declared grain
- Status breakdown
- Week-over-week growth rank
Edges to watch and interviewer follow-ups
Edges to watch
- Peak MB is not automatically MB-hours
- Task attempts and pipeline runs have different grains
Follow-ups
- What additional pricing dimensions are missing?
- How would you allocate shared clusters?
20 · Staffgovernance55 minutes
Subgroup quality guardrail
Scenario: A model promotion requires evidence that quality is not materially worse for a device or country subgroup.
Your task: For each production deployment, compare subgroup MAE by country and device family with its overall MAE. Flag a subgroup only when degradation exceeds 15%, label coverage is at least 60%, and sample size is at least 100.
Tables in play
prediction_eventsdeployments
Required output
- Overall and subgroup metrics
- Coverage and sample size
- Guardrail result
Edges to watch and interviewer follow-ups
Edges to watch
- Overall baseline must not be an unweighted average of subgroup MAEs
- Missing labels may be non-random
Follow-ups
- How would you correct for multiple testing?
- Is country a permissible governance dimension?
21 · Staffgovernance50 minutes
Safe data-retention deletion plan
Scenario: Dataset retention policies conflict with reproducibility requirements for deployed models.
Your task: Identify dataset versions past retention as of 2026-08-17, classify which are still referenced by model versions or feature materializations, and produce a deletion plan that preserves auditability.
Tables in play
datasetsdataset_versionsmodel_versionsfeature_materializationsdeployments
Required output
- Expired candidates
- Reference/production risk classification
- Ordered remediation plan
Edges to watch and interviewer follow-ups
Edges to watch
- Retention begins from a clearly chosen timestamp
- A foreign key prevents deletion but does not define archival policy
Follow-ups
- Would you retain metadata after deleting bytes?
- How do legal holds override retention?
22 · Staffserving55 minutes
Deduplicate serving events safely
Scenario: Client retries can duplicate request IDs and inflate both SLO and quality metrics.
Your task: Audit prediction_events for duplicate logical requests, define the correct uniqueness scope, select deterministic survivors, and design an online constraint migration that works with monthly partitioning.
Tables in play
prediction_events
Required output
- Duplicate audit query
- Survivor rule
- Partition-aware DDL and rollout
Edges to watch and interviewer follow-ups
Edges to watch
- request_id may only be unique within an endpoint or tenant
- A partitioned unique constraint must include the partition key in PostgreSQL 14
Follow-ups
- How would producers become idempotent?
- What happens to late cross-partition duplicates?
23 · Advancedserving45 minutes
Serving capacity recommendation
Scenario: SRE wants replica settings based on observed peak load and latency rather than static guesses.
Your task: 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.
Tables in play
deploymentsprediction_events
Required output
- Peak window and load
- Observed latency
- Documented capacity formula and recommendation
Edges to watch and interviewer follow-ups
Edges to watch
- The schema lacks per-replica throughput
- Multiple active deployments may share an endpoint
Follow-ups
- What telemetry is missing for a defensible recommendation?
- How would cold starts change headroom?
24 · Staffgovernance60 minutes
Reconstruct the production control plane
Scenario: During a postmortem, responders need to know exactly what model, traffic, config, and approval state existed at an arbitrary timestamp.
Your task: Return a production control-plane snapshot as of 2026-07-15 12:00 UTC: endpoint, model artifact, effective traffic, deployment config, approval state, and open incidents. Then identify which fields cannot be reconstructed reliably with the current schema.
Tables in play
deploymentsdeployment_trafficmodel_versionsgovernance_approvalsincidents
Required output
- One auditable snapshot relation
- Unknown/unrecoverable fields
- Bitemporal schema proposal
Edges to watch and interviewer follow-ups
Edges to watch
- Current rows are not necessarily historical truth
- Approval and config changes need valid-time history
Follow-ups
- Differentiate event time, valid time, and transaction time
- How would you test snapshot completeness?