Deploy Cadence

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

Problem

From the deploy_logs table, report each environment's number of deployments, its average deployment duration in seconds, and how many unique services have been deployed there, listed from the most deployments to the fewest. The logs spell environment and service names inconsistently, so names that differ only in letter case or surrounding spaces count as the same environment or service.

Which environments ship the most?

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 problem is really about

This is a three-measure environment summary with a data-cleaning problem hiding in the keys. The aggregates are the easy part: everyone writes COUNT(*), AVG(dur_secs) and a unique-service count over one grouping. What separates candidates is noticing that the log spells the same things several ways: 'production' and 'Production', 'staging' and 'STAGING', 'dev' and 'Dev', and one service as 'payment-api', 'Payment-API', ' payment-api' and 'payment-api '. Group on the raw env_name and you hand back seven environments where four exist, production's 66 deploys split 34 and 32, and a stray 'Dev' row with one deploy and no average. Count raw svc_name values and canary claims six services when it ran four.

Walking through it

Step 1: Clean the environment key before grouping

GROUP BY LOWER(env_name) folds the three case pairs into four environments. env_name has no padding, so case is the only fix it needs. Select the same LOWER(env_name) so the output label is exactly the group you formed.

Step 2: Count each service once, under one spelling

COUNT(DISTINCT LOWER(TRIM(svc_name))). DISTINCT turns a deploy tally into a service count, TRIM removes the stray spaces on ' payment-api' and 'payment-api ', and LOWER folds 'Payment-API'. Drop any one of the three and canary or staging gains a phantom service.

Step 3: Let the average skip missing durations

28 of the 200 deploys never logged dur_secs. AVG(dur_secs) divides by the non-NULL rows only, so dev reads 343.29. Hand-rolling SUM(dur_secs) / COUNT(*) divides by all 34 dev deploys and reports 282.71.

Step 4: Sort by deployment count, highest first

ORDER BY deploy_count DESC. Production and staging tie at 66 and canary and dev at 34; the question asks only for most deployments first, so either order inside a tie is correct.

The solution

SELECT
    LOWER(env_name) AS env_name,
    COUNT(*) AS deploy_count,
    AVG(dur_secs) AS avg_duration,
    COUNT(DISTINCT LOWER(TRIM(svc_name))) AS unique_services
FROM deploy_logs
GROUP BY LOWER(env_name)
ORDER BY deploy_count DESC

Raw keys

GROUP BY env_name with COUNT(DISTINCT svc_name): seven rows, 'Production' at 34 and 'production' at 32, canary at 6 services, 'STAGING' at 5, and a 'Dev' row with one deploy and a NULL average.

Cleaned keys

GROUP BY LOWER(env_name) with COUNT(DISTINCT LOWER(TRIM(svc_name))): four rows, production and staging at 66 deploys each, canary and dev at 34, and every environment running 4 services.

Common follow-up questions

• How would you add the most recent deploy per environment? (`MAX(deploy_at)` in the same pass.)
• How would you show the share of each environment's deploys that failed? (A conditional count over `status`, which is itself mixed-case ('failed', 'FAILED').)
• What if you needed monthly trends per environment? (Add a month bucket derived from `deploy_at` to the grouping key.)