# Engagement by Content Type

> Some content types get all the attention.

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

The content team uses total duration as a proxy for engagement and wants to know which formats consume the most viewer time. Show each content type's total duration in seconds, sorted from highest total duration to lowest.

## Worked solution and explanation

### Why this problem exists in real interviews

Content-type breakdowns test aggregation on a categorical dimension with a specific metric. The prompt asks for total duration as the engagement proxy, testing whether you read the requirements carefully.

---

### Break down the requirements

#### Step 1: Group by content_type

`GROUP BY content_type` produces one row per format.

#### Step 2: Sum duration as engagement proxy

`SUM(duration_seconds)` gives total viewer time per content type.

---

### The solution

**Total duration per content type**

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

> **Cost Analysis**
>
> Single pass with hash aggregation. Low cardinality on `content_type` means tiny hash table.

> **Interviewers Watch For**
>
> The interviewer checks whether you use the metric specified in the prompt (total duration) rather than defaulting to COUNT.

> **Common Pitfall**
>
> Using COUNT(*) alone measures volume, not engagement. The prompt specifically asks for total duration.

---

## Common follow-up questions

- How would you normalize by the number of items per type? _(Tests AVG(duration_seconds) for per-item engagement.)_
- What if some content has NULL duration? _(Tests NULL handling in SUM (ignores NULLs).)_
- How would you add a trend over time? _(Tests adding date dimension to GROUP BY.)_

## Related

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