Deployment Duration by Status

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Uber

Problem

deploy_logs holds one row per deploy with its service, its outcome and its duration in seconds, and the pipelines that wrote it spelled service names with inconsistent letter case and stray surrounding spaces, and outcomes with inconsistent letter case. Counting names that differ only in those ways as the same, total the deploy time for every service and outcome pairing.

Fast deploys versus slow ones. By outcome.

Tables

deploy_logs

Columns: log_id INTEGER, svc_name TEXT, version TEXT, env_name TEXT, status TEXT, deploy_at TEXT, dur_secs INTEGER, author TEXT

Expected output

Worked solution and explanation

What this really is

This is a two-key GROUP BY with a SUM, and the aggregation is not where anyone fails. The real problem is that the keys are dirty. svc_name holds 'payment-api', 'Payment-API', ' payment-api' and 'payment-api ', and status holds 'Success' beside 'success' and 'FAILED' beside 'failed'. Group on the stored values and one service fans out into four rows, every outcome appears twice, and each total is a fragment of the real one.

Getting there

Step 1: Clean svc_name with LOWER(TRIM(...))

TRIM strips the padded copies down to 'payment-api' or 'Payment-API', then LOWER folds the casing. Trimming first reads as the natural story: remove the noise, then compare. Both halves are load-bearing on this data.

Step 2: Clean status with LOWER

Outcomes drift in case but carry no padding, so LOWER(status) alone merges 'Success' with 'success' and 'FAILED' with 'failed'. A TRIM here would change nothing on this data, so it stays out.

Step 3: Group on the cleaned expressions and total

Group on the same cleaned expressions you select, not on the bare column names: in several engines GROUP BY svc_name resolves to the raw column rather than the alias, and the dirt comes straight back. SUM(dur_secs) skips deploys with no recorded time, so they add nothing. No sort is asked for.

SELECT
    LOWER(TRIM(svc_name)) AS svc_name,
    LOWER(status) AS status,
    SUM(dur_secs) AS total_duration
FROM deploy_logs
GROUP BY LOWER(TRIM(svc_name)), LOWER(status)

SELECT svc_name, status, SUM(dur_secs) AS total_duration
FROM deploy_logs
GROUP BY svc_name, status

Lowercase only

GROUP BY LOWER(svc_name), LOWER(status) merges 'Payment-API' into 'payment-api' but leaves ' payment-api' and 'payment-api ' as two more services, each with a partial total.

Trim and lowercase

LOWER(TRIM(svc_name)) collapses all four spellings into one key, so the service's total covers every one of its deploys.

Common follow-up questions

• How would you pivot this so each outcome is a column? (Tests the `SUM(CASE WHEN ... THEN dur_secs END)` pattern on the cleaned outcome.)
• How would you show each outcome's share of its service's total deploy time? (Tests `SUM(SUM(dur_secs)) OVER (PARTITION BY ...)` on the cleaned service as the denominator.)
• Where would you put this cleaning so every downstream query stops repeating it? (Tests fixing the dirt at write time or in a staging layer rather than in every query.)