# Deploy Count by Service

> Some services deploy constantly. Others barely at all.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The team is scoping a deployment automation project and wants to target the services that deploy most often first. Show each service's total deployment count, from the most frequently deployed down.

## Worked solution and explanation

### Why this problem exists in real interviews

This is a basic GROUP BY with COUNT and ORDER BY. It tests fundamental aggregation and sorting.

---

### Break down the requirements

#### Step 1: Group by service

`GROUP BY svc_name` with `COUNT(*)` for deployment count.

#### Step 2: Sort descending

`ORDER BY deploy_count DESC` surfaces the most frequently deployed first.

---

### The solution

**Simple deployment count per service**

```sql
SELECT svc_name, COUNT(*) AS deploy_count
FROM deploy_logs
GROUP BY svc_name
ORDER BY deploy_count DESC
```

> **Cost Analysis**
>
> Scan of 400K rows. Trivially fast.

> **Interviewers Watch For**
>
> Whether the candidate adds unnecessary complexity. This is a two-line query body.

> **Common Pitfall**
>
> Sorting ascending instead of descending would show the least deployed first, contradicting the prompt.

---

## Common follow-up questions

- How would you show only the top 5 services? _(Add LIMIT 5.)_
- How would you include services with zero deployments? _(Tests a service dimension table with LEFT JOIN.)_
- What if you needed the trend over time? _(Add a date dimension to the GROUP BY.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/deploy_count_by_service)
- [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.