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
- Filter production and July with half-open bounds.
- Extract segment using ->> and create a missing bucket.
- Cast feature_age_minutes to numeric only when the key exists.
- 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
- Extract and cast parameters.
- Partition version history by model.
- LAG each derived value in deterministic version order.
- 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?
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?
30 · Staff50 min
Dataset schema blast radius
Situation: A source schema changed and platform owners need the affected artifact and endpoint set.
Question: Detect schema_hash changes and list model versions and active production endpoints trained from changed versions.
Read the data at the right grain
Detect changes within each dataset's ordered version history, then traverse optional lineage outward with LEFT JOINs.
Tables: dataset_versions → datasets → model_versions → models → deployments
Construct the query
- LAG schema_hash by dataset version.
- Keep non-initial changed hashes.
- Join models trained from the changed version.
- Restrict endpoint impact to active production while preserving unconsumed changes.
Show the runnable worked query
WITH version_history AS (
SELECT dv.*,
lag(dv.schema_hash) OVER (
PARTITION BY dv.dataset_id
ORDER BY dv.version_no, dv.dataset_version_id
) AS previous_schema_hash
FROM dataset_versions dv
), schema_changes AS (
SELECT *
FROM version_history
WHERE previous_schema_hash IS NOT NULL
AND schema_hash <> previous_schema_hash
)
SELECT ds.dataset_name,
sc.version_no AS changed_version,
sc.previous_schema_hash,
sc.schema_hash,
mv.model_version_id,
m.model_name,
d.deployment_id,
d.endpoint_name
FROM schema_changes sc
JOIN datasets ds USING (dataset_id)
LEFT JOIN model_versions mv
ON mv.training_dataset_version_id = sc.dataset_version_id
LEFT JOIN models m USING (model_id)
LEFT JOIN deployments d
ON d.model_version_id = mv.model_version_id
AND d.environment = 'production'
AND d.status = 'active'
ORDER BY ds.dataset_name, sc.version_no, mv.model_version_id;
How to read the result
The output separates schema changes from the subset with registered or currently serving impact.
What makes this a senior/staff answer
Store structured schemas and compatibility results, not only hashes. Trigger contract checks before training and deployment rather than discovering blast radius afterward.
Interview traps and follow-ups
- Partition LAG by dataset
- Preserve changes with no downstream model
- A hash says changed, not compatible—what is missing?
- Where does feature lineage enter?
31 · Staff55 min
Deduplicate drift alert episodes
Situation: Weekly drift signals for several features generate a storm of overlapping pages.
Question: Merge overlapping breached windows per deployment and summarize each alert episode.
Read the data at the right grain
Interval islands start only when the next start is beyond every earlier end in the current partition. A running maximum handles nested overlaps.
Tables: drift_signals
Construct the query
- Filter breached signals.
- Compute the maximum prior window_end per deployment.
- Mark a new island when window_start exceeds that maximum.
- Cumulatively number and aggregate episodes.
Show the runnable worked query
WITH breached AS (
SELECT ds.*,
max(ds.window_end) OVER (
PARTITION BY ds.deployment_id
ORDER BY ds.window_start, ds.window_end, ds.drift_signal_id
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
) AS previous_max_end
FROM drift_signals ds
WHERE ds.score > ds.threshold
), marked AS (
SELECT *,
(previous_max_end IS NULL OR window_start > previous_max_end)::int AS starts_episode
FROM breached
), numbered AS (
SELECT *,
sum(starts_episode) OVER (
PARTITION BY deployment_id
ORDER BY window_start, window_end, drift_signal_id
) AS episode_id
FROM marked
)
SELECT deployment_id,
episode_id,
min(window_start) AS episode_start,
max(window_end) AS episode_end,
max(window_end) - min(window_start) AS duration,
count(*) AS signal_count,
count(DISTINCT feature_name) AS feature_count,
max(score / nullif(threshold, 0)) AS max_threshold_ratio
FROM numbered
GROUP BY deployment_id, episode_id
ORDER BY episode_start, deployment_id;
How to read the result
Many feature-level signals collapse into fewer operational episodes with severity and breadth context.
What makes this a senior/staff answer
Persist alert fingerprints and state transitions; SQL dedup alone does not implement acknowledgement, cooldown, escalation, or late-signal behavior.
Interview traps and follow-ups
- Compare with the running maximum end, not only the immediately previous row
- Do not merge across deployments
- Would touching intervals merge?
- How do you reopen an episode?
32 · Staff60 min
Rollback candidate and evidence
Situation: An incident commander needs a fast but defensible rollback option for each live endpoint.
Question: Find the closest prior production deployment, verify approval, and compare seven-day health evidence.
Read the data at the right grain
Candidate selection, compliance evidence, and health evidence are independent lateral lookups joined back to the active deployment.
Tables: deployments → governance_approvals → prediction_events → projects
Construct the query
- Anchor active production deployments.
- Lateral-select the closest earlier release in the same project.
- Use EXISTS for a prior approval without multiplying rows.
- Aggregate bounded current and historical health windows separately.
Show the runnable worked query
WITH active AS (
SELECT d.*
FROM deployments d
WHERE d.environment = 'production' AND d.status = 'active'
), candidates AS (
SELECT cur.*,
previous.deployment_id AS rollback_deployment_id,
previous.model_version_id AS rollback_model_version_id
FROM active cur
LEFT JOIN LATERAL (
SELECT d.deployment_id, d.model_version_id
FROM deployments d
WHERE d.project_id = cur.project_id
AND d.environment = 'production'
AND d.deployed_at < cur.deployed_at
ORDER BY d.deployed_at DESC, d.deployment_id DESC
LIMIT 1
) AS previous ON true
), evidence AS (
SELECT c.*,
EXISTS (
SELECT 1
FROM governance_approvals ga
WHERE ga.model_version_id = c.rollback_model_version_id
AND ga.environment = 'production'
AND ga.decision = 'approved'
AND ga.decided_at <= c.deployed_at
) AS rollback_was_approved,
current_health.error_rate AS current_error_rate,
current_health.p95_latency_ms AS current_p95_latency_ms,
previous_health.error_rate AS previous_error_rate,
previous_health.p95_latency_ms AS previous_p95_latency_ms
FROM candidates c
LEFT JOIN LATERAL (
SELECT avg((pe.http_status >= 500)::int) AS error_rate,
percentile_cont(0.95) WITHIN GROUP (ORDER BY pe.latency_ms) AS p95_latency_ms
FROM prediction_events pe
WHERE pe.deployment_id = c.deployment_id
AND pe.predicted_at >= timestamptz '2026-08-10 00:00+00'
AND pe.predicted_at < timestamptz '2026-08-17 00:00+00'
) AS current_health ON true
LEFT JOIN LATERAL (
SELECT avg((pe.http_status >= 500)::int) AS error_rate,
percentile_cont(0.95) WITHIN GROUP (ORDER BY pe.latency_ms) AS p95_latency_ms
FROM prediction_events pe
WHERE pe.deployment_id = c.rollback_deployment_id
AND pe.predicted_at >= c.deployed_at - interval '7 days'
AND pe.predicted_at < c.deployed_at
) AS previous_health ON true
)
SELECT p.project_name,
e.deployment_id,
e.rollback_deployment_id,
e.rollback_was_approved,
e.current_error_rate,
e.previous_error_rate,
e.current_p95_latency_ms,
e.previous_p95_latency_ms,
e.rollback_was_approved
AND e.previous_error_rate IS NOT NULL
AND e.previous_p95_latency_ms IS NOT NULL AS has_minimum_rollback_evidence
FROM evidence e
JOIN projects p USING (project_id)
ORDER BY p.project_name;
How to read the result
Each endpoint gets a candidate and an explicit minimum-evidence flag rather than an unconditional rollback recommendation.
What makes this a senior/staff answer
A real rollback gate also verifies artifact availability, feature/schema compatibility, config, capacity, security revocation, and a rehearsed traffic-switch procedure.
Interview traps and follow-ups
- Previous does not mean safe
- Historical traffic cohorts may differ
- What if the prior artifact is incompatible with today's features?
- How do you precompute rollback readiness?
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
- Filter the deployment population to active production.
- LEFT JOIN incidents by project and time window.
- Keep right-table predicates inside ON.
- 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
- Join events to deployment metadata.
- Filter production traffic and July using half-open bounds.
- Group by endpoint and event date.
- 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
- Start at model-version grain.
- INNER JOIN model and project dimensions.
- LEFT JOIN optional training dataset lineage.
- 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
- Join each successful run to its validation AUC row.
- Keep model_id as the partition key.
- ROW_NUMBER by AUC descending and run_id ascending.
- 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
- Generate the seven calendar dates.
- Cross every active pipeline with every date.
- LEFT JOIN runs using half-open daily bounds.
- 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
- Anchor on all projects.
- LEFT JOIN incidents.
- COUNT incident_id for the total.
- 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?