# Deployment Duration by Status > Fast deploys versus slow ones. By outcome. Canonical URL: 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. ## Tables ### deploy_logs - log_id INTEGER - svc_name TEXT - version TEXT - env_name TEXT - status TEXT - deploy_at TEXT - dur_secs INTEGER - author TEXT Sample rows (10 of 200): | log_id | svc_name | version | env_name | status | deploy_at | dur_secs | author | | --- | --- | --- | --- | --- | --- | --- | --- | | 131 | payment-api | v1.0.2 | staging | failed | 2026-02-02 01:11:00 | 23 | bob | | 162 | user-svc | v1.1.0 | dev | rolled_back | 2026-03-03 02:22:00 | 36 | charlie | | 193 | search-api | 2.0.0 | canary | in_progress | 2026-04-04 03:33:00 | 49 | dana | | 224 | gateway | v2.1.0 | Production | Success | 2026-05-05 04:44:00 | 62 | eve | | 255 | notif-svc | 2.1.1 | STAGING | FAILED | 2026-06-06 05:55:00 | 75 | frank | | 286 | analytics | v3.0.0-rc1 | production | success | 2026-07-07 06:06:00 | 88 | Alice | | 317 | ml-serving | v1.0.3 | staging | failed | 2026-08-08 07:17:00 | NULL | BOB | | 348 | auth-svc | v1.0.1 | dev | Rolled_Back | 2026-09-09 08:28:00 | 114 | alice | | 379 | Payment-API | v1.0.2 | canary | in_progress | 2026-01-10 09:39:00 | 127 | bob | | 410 | user-svc | v1.1.0 | Production | Success | 2026-02-11 10:50:00 | 140 | charlie | ## Expected output First 5 of 16 rows: | svc_name | status | total_duration | | --- | --- | --- | | analytics | rolled_back | 2496 | | analytics | success | 3920 | | auth-svc | rolled_back | 1980 | | ml-serving | failed | 3366 | | ml-serving | in_progress | 2526 | ## 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. --- **Total deploy time per real service and outcome** ```sql 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 the cleaned keys and group on exactly those expressions.* **The raw version that fragments** ```sql SELECT svc_name, status, SUM(dur_secs) AS total_duration FROM deploy_logs GROUP BY svc_name, status ``` *Every spelling becomes its own key.* **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. > **The padding hides better than the casing** > > Candidates who spot 'Success' and 'success' often fix the casing and stop. The padded service names are invisible in a quick scan of sample rows, and they quietly leave duplicate keys in the result. > **Look at the keys before you trust them** > > Strong candidates check the keys before aggregating: a quick look at the stored values of `svc_name` and `status` reveals the drift, and they clean it once, up front, rather than patching the output. > **One pass, a handful of keys** > > The string functions run once per row during the single scan of `deploy_logs`; the hash aggregate holds only as many entries as there are cleaned pairings. The cost is the scan, not the cleaning. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/deployment_duration_by_status) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.