Thirty Days of Shipping

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

Zest AI

Problem

A platform engineering team tracks how many separate deployments ship each month, where a deployment is a unique pairing of service and version. Walk the timeline from the earliest month onward, showing each month as 'YYYY-MM' next to its count of unique deployments.

A month in the life of an engineering team, counted one deploy at a time.

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

Why this problem exists in real interviews

Querying deploy_logs for svc_name data using grouping and date extraction tests whether you can translate a business requirement into the right column references and filter sequence. It shows up as a fundamentals check to verify practical fluency.

Break down the requirements

Step 1: Aggregate with COUNT DISTINCT

Group by the output grain and apply COUNT DISTINCT() to compute the metric. The GROUP BY must match exactly what the output needs: one row per group key.

Step 2: Order the final output

Apply ORDER BY as specified to produce the expected row sequence. When tied values exist, add a secondary sort column for determinism.

The solution

SELECT STRFTIME('%Y-%m', deploy_at) AS month,
    COUNT(DISTINCT svc_name || ':' || version) AS unique_deployments
FROM deploy_logs
GROUP BY STRFTIME('%Y-%m', deploy_at)
ORDER BY month

Common follow-up questions

• What happens to your result if `deploy_logs`.dur_secs contains NULLs for some rows? (Tests whether the candidate accounts for NULL behavior in aggregates and comparisons on `dur_secs`.)
• How would you verify that your aggregation on `deploy_logs`.log_id is not double-counting due to duplicate rows? (Tests data quality awareness and deduplication strategies.)
• With millions of distinct values in `deploy_logs`.log_id, what index strategy would you use to keep this query performant? (Tests indexing knowledge specific to high-cardinality columns like `log_id`.)