# Monthly Deploy Counts Pivoted

> Deploys by month. Side by side.

Canonical URL: <https://datadriven.io/problems/monthly_deploy_counts_pivoted>

Domain: SQL · Difficulty: hard · Seniority: L4

## Problem

Show monthly deployment counts for the 'production' environment, pivoted into separate columns for January, May, and November. Results should be broken down by year.

## Worked solution and explanation

### Why this problem exists in real interviews

The interviewer wants to see you apply pivot and conditional aggregation to deploy_logs.svc_name while accounting for the distribution of version. This surfaces in senior-level rounds because small logic errors produce results that look correct at a glance.

> **Trick to Solving**
>
> SQL lacks a native PIVOT operator in most dialects. The trick is conditional aggregation with `CASE WHEN` inside aggregate functions.
> 
> 1. Identify the column whose values become output columns
> 2. Write `SUM(CASE WHEN col = 'val' THEN metric END)` for each pivot value
> 3. Group by the row identifier

---

### Break down the requirements

#### Step 1: Filter to the target rows

Apply the date filter using `STRFTIME` to extract and compare the relevant time component. This restricts rows before aggregation.

#### Step 2: Aggregate with SUM

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

#### Step 3: Pivot with CASE WHEN

Each `CASE WHEN` expression inside an aggregate function transforms a row value into a column. This is the standard cross-tabulation pattern in SQL.

#### Step 4: 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

**CASE WHEN pivot for selected months**

```sql
SELECT STRFTIME('%Y', deploy_at) AS year,
    SUM(CASE WHEN CAST(STRFTIME('%m', deploy_at) AS INTEGER) = 1 THEN 1 ELSE 0 END) AS jan,
    SUM(CASE WHEN CAST(STRFTIME('%m', deploy_at) AS INTEGER) = 5 THEN 1 ELSE 0 END) AS may,
    SUM(CASE WHEN CAST(STRFTIME('%m', deploy_at) AS INTEGER) = 11 THEN 1 ELSE 0 END) AS nov
FROM deploy_logs
WHERE env_name = 'production'
GROUP BY STRFTIME('%Y', deploy_at)
ORDER BY year
```

> **Cost Analysis**
>
> The query scans 2M rows from `deploy_logs`. The aggregation reduces the row count before any downstream processing, which is the key performance lever.

> **Interviewers Watch For**
>
> Naming the output grain ("one row per X") before writing the GROUP BY shows you think about data shape, not just syntax. Knowing that conditional aggregation replaces PIVOT in standard SQL is a strong signal of cross-dialect experience.

> **Common Pitfall**
>
> Comparing dates stored as TEXT without casting can produce lexicographic instead of chronological ordering. Always confirm the column type.

---

## 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.)_
- What happens to your CASE expressions if a new category value appears in svc_name, version? _(Tests whether the candidate recognizes that hard-coded CASE values miss future categories.)_
- 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/monthly_deploy_counts_pivoted)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.