# Content Type Distribution

> How many of each content type?

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The editorial team wants a quick census of the content library. How many items exist for each content type? List them alphabetically.

## Worked solution and explanation

### Why this problem exists in real interviews

This is a basic GROUP BY with COUNT and ORDER BY. It tests the simplest form of aggregation: counting rows per category.

---

### Break down the requirements

#### Step 1: Group by content type

`GROUP BY content_type` produces one row per type.

#### Step 2: Count and sort

`COUNT(*)` for item count, `ORDER BY content_type` for alphabetical listing.

---

### The solution

**Simple count per category**

```sql
SELECT content_type, COUNT(*) AS item_count
FROM content_items
GROUP BY content_type
ORDER BY content_type
```

> **Cost Analysis**
>
> Scan of 1M rows with low-cardinality GROUP BY. Trivially fast.

> **Interviewers Watch For**
>
> Whether the candidate adds unnecessary complexity. This is a one-statement query; anything more is over-engineering.

> **Common Pitfall**
>
> Sorting by item_count instead of content_type would violate the "alphabetically" requirement. Always match the prompt's sort specification.

---

## Common follow-up questions

- How would you add a percentage column? _(Tests 100.0 * COUNT(*) / SUM(COUNT(*)) OVER () for the share.)_
- What if content_type had NULLs? _(They form a NULL group. Tests whether to exclude or label them.)_
- How would you show only types with more than 100 items? _(Add HAVING COUNT(*) > 100.)_

## Related

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