# Views by Content Type

> Count content views broken down by content type

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

The content team is prioritizing which formats to promote based on engagement. How many views has each content type received?

## Worked solution and explanation

### Why this problem exists in real interviews

This tests a two-table join with GROUP BY on a dimension attribute. Interviewers check whether you can aggregate a fact table by a dimension column that requires a join.

---

### Break down the requirements

#### Step 1: Join views to content items

`JOIN content_items ON content_views.content_id = content_items.content_id` enriches each view with the content type.

#### Step 2: Group by content type and count

`GROUP BY content_type` with `COUNT(*)` produces the view count per type.

---

### The solution

**Dimension join with grouped count**

```sql
SELECT ci.content_type, COUNT(*) AS view_count
FROM content_views cv
JOIN content_items ci ON cv.content_id = ci.content_id
GROUP BY ci.content_type
```

> **Cost Analysis**
>
> The join matches 600M views to 300K content items. With an index on `content_items(content_id)`, each lookup is O(1). The GROUP BY reduces to 5 content types. The full scan of 600M views is the bottleneck.

> **Interviewers Watch For**
>
> Whether you use INNER JOIN or LEFT JOIN. Since we want view counts per type, INNER JOIN is correct; views without a matching content item would represent data integrity issues.

> **Common Pitfall**
>
> Grouping by `content_id` instead of `content_type`. This would give per-item counts, not per-type totals.

---

## Common follow-up questions

- How would you include content types with zero views? _(Change to RIGHT JOIN from content_items or use a LEFT JOIN starting from content_items.)_
- How would you add the average watch duration per content type? _(Add AVG(cv.watch_seconds) to the SELECT.)_
- What if content_id in content_views has orphan references? _(Use LEFT JOIN and check for NULLs to quantify orphan views.)_

## Related

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