Skip to content
Apple MLOps Interview Lab

Core SQL exercises

Each lesson explains the source rows, output grain, query construction, runnable solution, result meaning, and production follow-ups.

01 · Core20 min

Deployment cadence by project

Situation: Platform leadership wants to understand how frequently production models change.

Question: For every project, show each production deployment and the hours since its preceding production deployment. Retain the first deployment with a NULL interval.

Read the data at the right grain

The output grain is one production deployment. LAG reads the preceding row without collapsing the current row, so it is a better fit than grouping deployments into projects.

Tables: projects → deployments

Construct the query

  1. Filter to production before the window so dev and staging never become the previous row.
  2. Partition by project_id and order by deployed_at plus deployment_id for deterministic ties.
  3. Subtract the lagged timestamp and convert PostgreSQL's interval to hours.
  4. Keep the NULL produced for each project's first deployment.
Show the runnable worked query
SELECT p.project_name,
       d.deployment_id,
       d.deployed_at,
       extract(epoch FROM d.deployed_at - lag(d.deployed_at) OVER (
         PARTITION BY d.project_id
         ORDER BY d.deployed_at, d.deployment_id
       )) / 3600.0 AS hours_since_previous
FROM deployments d
JOIN projects p USING (project_id)
WHERE d.environment = 'production'
ORDER BY p.project_name, d.deployed_at, d.deployment_id;

How to read the result

Each row is a release. A NULL interval means this is the first observed production release for that project.

What makes this a senior/staff answer

The existing (project_id, environment, deployed_at) index supports this access pattern. Decide whether rolled-back releases still count toward cadence; this example keeps them because they were real changes.

Interview traps and follow-ups
  • Partition the window by project
  • Do not drop the first deployment
  • How would you calculate median cadence by team?
  • What index supports this query?
02 · Core30 min

Weekly training health

Situation: The training platform needs a weekly reliability scorecard for each ML product.

Question: 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.

Read the data at the right grain

First derive row-level durations, then aggregate at project-week grain. Separating those phases makes timestamp null handling and metric definitions visible.

Tables: projects → models → experiments → training_runs

Construct the query

  1. Join run → experiment → model to recover project_id.
  2. Use queued_at to assign the calendar week and compute queue/runtime seconds per run.
  3. Turn status predicates into 0/1 values so AVG becomes a rate whose denominator includes cancelled runs.
  4. Use ordered-set percentiles and HAVING after aggregation.
Show the runnable worked query
WITH run_durations AS (
  SELECT m.project_id,
         date_trunc('week', r.queued_at) AS week_start,
         r.status,
         extract(epoch FROM r.started_at - r.queued_at) AS queue_seconds,
         extract(epoch FROM r.finished_at - r.started_at) AS runtime_seconds
  FROM training_runs r
  JOIN experiments e USING (experiment_id)
  JOIN models m USING (model_id)
)
SELECT p.project_name,
       rd.week_start,
       count(*) AS run_count,
       avg((rd.status = 'succeeded')::int) AS success_rate,
       avg((rd.status = 'failed')::int) AS failure_rate,
       percentile_cont(0.50) WITHIN GROUP (ORDER BY rd.queue_seconds)
         FILTER (WHERE rd.queue_seconds IS NOT NULL) AS p50_queue_seconds,
       percentile_cont(0.95) WITHIN GROUP (ORDER BY rd.runtime_seconds)
         FILTER (WHERE rd.runtime_seconds IS NOT NULL) AS p95_runtime_seconds
FROM run_durations rd
JOIN projects p USING (project_id)
GROUP BY p.project_name, rd.week_start
HAVING count(*) >= 20
ORDER BY rd.week_start, p.project_name;

How to read the result

One row describes a project-week with at least 20 queued runs, including reliability rates and tail timing.

What makes this a senior/staff answer

Choose a business timezone explicitly. Recent weeks may be incomplete, and retries may need a logical-run denominator instead of physical attempts.

Interview traps and follow-ups
  • Cancelled runs belong in the denominator
  • NULL timestamps must not break percentiles
  • Would you use mean or percentile for runtime?
  • How would retries alter the metric?
03 · Core20 min

Effective rollout traffic

Situation: An incident commander needs the exact traffic allocation that was active at a historical instant.

Question: Find the effective traffic percentage for every active deployment as of 2026-07-15 12:00 UTC. Do not use a correlated scalar subquery.

Read the data at the right grain

An as-of query means: discard future facts, rank the remaining history newest-first, and retain rank one per business key.

Tables: deployments → deployment_traffic

Construct the query

  1. Declare the as-of timestamp once conceptually.
  2. Restrict deployments and traffic changes to facts visible by that instant.
  3. ROW_NUMBER each deployment's traffic history by effective_at descending.
  4. Select recency = 1 after ranking.
Show the runnable worked query
WITH ranked_traffic AS (
  SELECT d.deployment_id,
         d.endpoint_name,
         dt.effective_at,
         dt.traffic_percent,
         row_number() OVER (
           PARTITION BY d.deployment_id
           ORDER BY dt.effective_at DESC
         ) AS recency
  FROM deployments d
  JOIN deployment_traffic dt USING (deployment_id)
  WHERE d.environment = 'production'
    AND d.status = 'active'
    AND d.deployed_at <= timestamptz '2026-07-15 12:00+00'
    AND dt.effective_at <= timestamptz '2026-07-15 12:00+00'
)
SELECT deployment_id, endpoint_name, effective_at, traffic_percent
FROM ranked_traffic
WHERE recency = 1
ORDER BY endpoint_name, deployment_id;

How to read the result

At most one traffic record is returned per currently active deployment that existed at noon UTC on July 15.

What makes this a senior/staff answer

Current status is not historical status. A truly auditable answer needs effective-dated deployment state, not only effective-dated traffic.

Interview traps and follow-ups
  • Exclude future traffic changes
  • Some deployments may have no earlier traffic record
  • Compare DISTINCT ON with ROW_NUMBER
  • How would you model rollout end times?

More Core walkthroughs

Section titled “More Core walkthroughs”

These additional lessons reinforce the same fundamentals with smaller, interview-friendly joins, aggregates, windows, and date-spine problems.

25 · Core25 min

JSONB serving segmentation

Situation: Serving telemetry stores a few evolving dimensions inside input_features.

Question: By JSON feature segment, report July production volume, average feature age, p95 latency, and error rate. Handle missing keys.

Read the data at the right grain

Extract JSON values at event grain, cast only numeric fields, then aggregate. COALESCE makes schema evolution visible instead of silently dropping missing segments.

Tables: prediction_events → deployments

Construct the query

  1. Filter production and July with half-open bounds.
  2. Extract segment using ->> and create a missing bucket.
  3. Cast feature_age_minutes to numeric only when the key exists.
  4. Aggregate counts, percentile, and boolean error rate.
Show the runnable worked query
SELECT coalesce(pe.input_features ->> 'segment', '(missing)') AS segment,
       count(*) AS requests,
       avg((pe.input_features ->> 'feature_age_minutes')::numeric)
         FILTER (WHERE pe.input_features ? 'feature_age_minutes') AS avg_feature_age_minutes,
       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 prediction_events pe
JOIN deployments d USING (deployment_id)
WHERE d.environment = 'production'
  AND pe.predicted_at >= timestamptz '2026-07-01 00:00+00'
  AND pe.predicted_at <  timestamptz '2026-08-01 00:00+00'
GROUP BY coalesce(pe.input_features ->> 'segment', '(missing)')
ORDER BY requests DESC, segment;

How to read the result

A compact segment scorecard shows whether stale features correlate with serving behavior.

What makes this a senior/staff answer

Promote stable, frequently queried keys to typed columns. GIN helps containment predicates, not necessarily a full grouping scan.

Interview traps and follow-ups
  • ->> returns text
  • A missing key casts to NULL
  • When should this become a typed column?
  • Would the GIN index help this grouping?
26 · Core25 min

Model parameter changes

Situation: Reviewers need to see material hyperparameter changes between registered versions.

Question: Show max_depth and learning_rate for every model version beside its predecessor and flag learning-rate changes over 50%.

Read the data at the right grain

Treat the JSON keys as typed derived columns, then use LAG exactly as you would for ordinary versioned attributes.

Tables: models → model_versions

Construct the query

  1. Extract and cast parameters.
  2. Partition version history by model.
  3. LAG each derived value in deterministic version order.
  4. Calculate relative change with NULLIF.
Show the runnable worked query
WITH version_parameters AS (
  SELECT m.model_name,
         mv.model_version_id,
         mv.version_no,
         (mv.parameters ->> 'max_depth')::integer AS max_depth,
         (mv.parameters ->> 'learning_rate')::numeric AS learning_rate,
         lag((mv.parameters ->> 'max_depth')::integer) OVER model_history AS previous_max_depth,
         lag((mv.parameters ->> 'learning_rate')::numeric) OVER model_history AS previous_learning_rate
  FROM model_versions mv
  JOIN models m USING (model_id)
  WINDOW model_history AS (
    PARTITION BY mv.model_id ORDER BY mv.version_no, mv.model_version_id
  )
)
SELECT *,
       abs(learning_rate / nullif(previous_learning_rate, 0) - 1) > 0.50
         AS learning_rate_changed_over_50pct
FROM version_parameters
ORDER BY model_name, version_no;

How to read the result

The first model version has NULL predecessors; subsequent rows expose both the values and material-change flag.

What makes this a senior/staff answer

A governed registry should validate parameter schemas and retain a canonical configuration digest, not depend on ad hoc JSON casts.

Interview traps and follow-ups
  • Version ordering is per model
  • Divide by zero with NULLIF
  • How do you diff arbitrary JSON?
  • Should parameter schemas be versioned?
33 · Core20 min

Keep deployments with zero incidents

Situation: A reliability inventory must show healthy endpoints as well as endpoints with incidents.

Question: For every active production deployment, count incidents detected in the 30 days ending 2026-08-17. Keep zero-incident deployments.

Read the data at the right grain

The deployment is the required population, so start there and LEFT JOIN optional incidents. COUNT a nullable incident key to turn no match into zero.

Tables: deployments → projects → incidents

Construct the query

  1. Filter the deployment population to active production.
  2. LEFT JOIN incidents by project and time window.
  3. Keep right-table predicates inside ON.
  4. Group by the deployment key and COUNT incident_id.
Show the runnable worked query
SELECT p.project_name,
       d.deployment_id,
       d.endpoint_name,
       count(i.incident_id) AS incidents_last_30d
FROM deployments d
JOIN projects p USING (project_id)
LEFT JOIN incidents i
  ON i.project_id = d.project_id
 AND i.detected_at >= timestamptz '2026-07-18 00:00+00'
 AND i.detected_at <  timestamptz '2026-08-17 00:00+00'
WHERE d.environment = 'production'
  AND d.status = 'active'
GROUP BY p.project_name, d.deployment_id, d.endpoint_name
ORDER BY p.project_name, d.deployment_id;

How to read the result

All 12 active production deployments remain visible, including rows whose incident count is zero.

What makes this a senior/staff answer

Project-level attribution can over-associate incidents when a project has several endpoints. Prefer a service/deployment component key where incident metadata supports it.

Interview traps and follow-ups
  • COUNT(*) would count the preserved deployment row
  • Put incident date filters in ON, not WHERE
  • Should incidents join by project or deployment?
  • How would you count only SEV1/SEV2?
34 · Core25 min

Daily HTTP status buckets

Situation: Serving owners need a daily endpoint health table without writing four separate queries.

Question: By endpoint and July day, count successful, client-error, and server-error requests and calculate server-error rate.

Read the data at the right grain

FILTER performs several conditional counts over one grouped event population. The total count remains the shared rate denominator.

Tables: prediction_events → deployments

Construct the query

  1. Join events to deployment metadata.
  2. Filter production traffic and July using half-open bounds.
  3. Group by endpoint and event date.
  4. Count each status range with FILTER and guard the division.
Show the runnable worked query
SELECT d.endpoint_name,
       pe.predicted_at::date AS day,
       count(*) FILTER (WHERE pe.http_status BETWEEN 200 AND 399) AS successful_requests,
       count(*) FILTER (WHERE pe.http_status BETWEEN 400 AND 499) AS client_errors,
       count(*) FILTER (WHERE pe.http_status >= 500) AS server_errors,
       count(*) FILTER (WHERE pe.http_status >= 500)::numeric
         / nullif(count(*), 0) AS server_error_rate
FROM prediction_events pe
JOIN deployments d USING (deployment_id)
WHERE d.environment = 'production'
  AND pe.predicted_at >= timestamptz '2026-07-01 00:00+00'
  AND pe.predicted_at <  timestamptz '2026-08-01 00:00+00'
GROUP BY d.endpoint_name, pe.predicted_at::date
ORDER BY day, d.endpoint_name;

How to read the result

Each observed endpoint-day has mutually understandable volume buckets and an error rate between zero and one.

What makes this a senior/staff answer

Define status taxonomy centrally and generate a date spine when missing days must be distinguishable from zero errors.

Interview traps and follow-ups
  • Use the same denominator for the rate
  • Keep partition-prunable timestamp bounds
  • Where do redirects belong?
  • How would you include zero-traffic days?
35 · Core30 min

Join artifact lineage

Situation: A model registry screen needs artifact, training-data, and deployment context in one relation.

Question: Show every model version with project, model, training dataset version, schema hash, artifact digest, and deployment count.

Read the data at the right grain

Anchor on model_versions because every registry artifact must appear. Join required owners inward and optional training/deployment context outward.

Tables: projects → models → model_versions → dataset_versions → datasets → deployments

Construct the query

  1. Start at model-version grain.
  2. INNER JOIN model and project dimensions.
  3. LEFT JOIN optional training dataset lineage.
  4. LEFT JOIN deployments and aggregate back to one artifact row.
Show the runnable worked query
SELECT p.project_name,
       m.model_name,
       mv.model_version_id,
       mv.version_no AS model_version,
       ds.dataset_name,
       dv.version_no AS dataset_version,
       dv.schema_hash,
       mv.artifact_digest,
       count(d.deployment_id) AS deployment_count
FROM model_versions mv
JOIN models m USING (model_id)
JOIN projects p USING (project_id)
LEFT JOIN dataset_versions dv
  ON dv.dataset_version_id = mv.training_dataset_version_id
LEFT JOIN datasets ds USING (dataset_id)
LEFT JOIN deployments d USING (model_version_id)
GROUP BY p.project_name, m.model_name, mv.model_version_id, mv.version_no,
         ds.dataset_name, dv.version_no, dv.schema_hash, mv.artifact_digest
ORDER BY p.project_name, m.model_name, mv.version_no;

How to read the result

Every registered artifact appears once, with nullable data lineage and a zero-capable deployment count.

What makes this a senior/staff answer

A deployment count does not prove which feature snapshot or code environment produced the artifact. Store immutable manifests and attestations.

Interview traps and follow-ups
  • Deployments are one-to-many
  • Optional lineage requires LEFT JOIN
  • Why group after joining deployments?
  • What lineage is still missing?
36 · Core30 min

Top three runs per model

Situation: Model owners want a short validation leaderboard for each model.

Question: Return the three succeeded runs with the highest validation AUC per model, breaking ties by smaller run_id.

Read the data at the right grain

Top N per group is rank-then-filter: first create one scored row per run, then assign a row number inside each model.

Tables: training_runs → run_metrics → experiments → models

Construct the query

  1. Join each successful run to its validation AUC row.
  2. Keep model_id as the partition key.
  3. ROW_NUMBER by AUC descending and run_id ascending.
  4. Filter ranks one through three in an outer query.
Show the runnable worked query
WITH scored_runs AS (
  SELECT m.model_id,
         m.model_name,
         r.run_id,
         rm.metric_value AS validation_auc
  FROM training_runs r
  JOIN experiments e USING (experiment_id)
  JOIN models m USING (model_id)
  JOIN run_metrics rm
    ON rm.run_id = r.run_id
   AND rm.metric_name = 'auc'
   AND rm.split = 'validation'
   AND rm.step = 0
  WHERE r.status = 'succeeded'
), ranked AS (
  SELECT *,
         row_number() OVER (
           PARTITION BY model_id
           ORDER BY validation_auc DESC, run_id
         ) AS auc_rank
  FROM scored_runs
)
SELECT model_name, run_id, validation_auc, auc_rank
FROM ranked
WHERE auc_rank <= 3
ORDER BY model_name, auc_rank;

How to read the result

Each model contributes no more than three deterministic leaderboard rows.

What makes this a senior/staff answer

Metric definitions, dataset versions, and evaluation timestamps must match before scores are genuinely comparable.

Interview traps and follow-ups
  • Filter the metric before ranking
  • LIMIT 3 would be global, not per model
  • When would DENSE_RANK be preferable?
  • How should equal metrics behave?
37 · Core30 min

Find missing pipeline days

Situation: A pipeline that never ran is absent from the run table, but absence is exactly what operations needs to detect.

Question: For July 1–7, show every active pipeline-day with scheduled runs, successes, and a missing-schedule flag.

Read the data at the right grain

Facts cannot report missing facts. CROSS JOIN active pipelines with a date spine to create the expected population, then LEFT JOIN observed runs.

Tables: pipeline_definitions → pipeline_runs

Construct the query

  1. Generate the seven calendar dates.
  2. Cross every active pipeline with every date.
  3. LEFT JOIN runs using half-open daily bounds.
  4. Count run IDs and compare the count with zero.
Show the runnable worked query
WITH days AS (
  SELECT day::date
  FROM generate_series(date '2026-07-01', date '2026-07-07', interval '1 day') AS g(day)
), pipeline_days AS (
  SELECT pd.pipeline_id, pd.pipeline_name, days.day
  FROM pipeline_definitions pd
  CROSS JOIN days
  WHERE pd.active
)
SELECT pd.pipeline_name,
       pd.day,
       count(pr.pipeline_run_id) AS scheduled_runs,
       count(pr.pipeline_run_id) FILTER (WHERE pr.status = 'succeeded') AS succeeded_runs,
       count(pr.pipeline_run_id) = 0 AS missing_schedule
FROM pipeline_days pd
LEFT JOIN pipeline_runs pr
  ON pr.pipeline_id = pd.pipeline_id
 AND pr.scheduled_for >= pd.day::timestamptz
 AND pr.scheduled_for <  pd.day::timestamptz + interval '1 day'
GROUP BY pd.pipeline_name, pd.day
ORDER BY pd.day, pd.pipeline_name;

How to read the result

The result has a stable expected row count and explicitly marks pipeline-days with no scheduled run.

What makes this a senior/staff answer

A true scheduler audit expands each cron expression, respects activation history and timezone, and distinguishes scheduler omission from delayed ingestion.

Interview traps and follow-ups
  • Generate expected rows before joining facts
  • Cast/report dates in an explicit timezone
  • How would cron determine expected days?
  • What about late-created pipelines?
38 · Core20 min

Pivot incident severity counts

Situation: Leadership wants a compact incident distribution without a client-side pivot.

Question: Return one row per project with total, SEV1, SEV2, SEV3, and SEV4 incident counts, retaining zero-incident projects.

Read the data at the right grain

A fixed-category pivot is several filtered aggregates over the same left-joined population.

Tables: projects → incidents

Construct the query

  1. Anchor on all projects.
  2. LEFT JOIN incidents.
  3. COUNT incident_id for the total.
  4. Repeat COUNT with one FILTER per severity.
Show the runnable worked query
SELECT p.project_name,
       count(i.incident_id) AS total_incidents,
       count(i.incident_id) FILTER (WHERE i.severity = 'SEV1') AS sev1_incidents,
       count(i.incident_id) FILTER (WHERE i.severity = 'SEV2') AS sev2_incidents,
       count(i.incident_id) FILTER (WHERE i.severity = 'SEV3') AS sev3_incidents,
       count(i.incident_id) FILTER (WHERE i.severity = 'SEV4') AS sev4_incidents
FROM projects p
LEFT JOIN incidents i USING (project_id)
GROUP BY p.project_name
ORDER BY total_incidents DESC, p.project_name;

How to read the result

Every project appears, and the four severity counts should add up to the total incident count.

What makes this a senior/staff answer

Hard-coded pivots are presentation-friendly but schema-rigid. For APIs, a severity rowset or JSON aggregate may evolve more safely.

Interview traps and follow-ups
  • COUNT incident_id, not COUNT star
  • Severity columns should reconcile to total
  • How do new severity values affect this query?
  • Would rows be more extensible than columns?