# Content Viewer Penetration

> What share of the user base has viewed at least one piece of content

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The growth team is measuring content adoption across the user base. What percentage of all registered users have at least one content view on record? Users who have never viewed anything should still count toward the total.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests a percentage computation using subqueries or JOINs with COUNT(DISTINCT). The requirement that non-viewers count toward the total probes whether you use the users table as the denominator, not content_views.

---

### Break down the requirements

#### Step 1: Count users with at least one view

`COUNT(DISTINCT user_id)` from `content_views` gives the number of users who have viewed content.

#### Step 2: Count total registered users

`COUNT(*)` from `users` gives the denominator.

#### Step 3: Compute the percentage

Divide viewers by total users and multiply by 100.

---

### The solution

**Penetration rate via dual-table counts**

```sql
SELECT
    ROUND(100.0 * (SELECT COUNT(DISTINCT user_id) FROM content_views) / (SELECT COUNT(*) FROM users), 2) AS penetration_pct
```

> **Cost Analysis**
>
> Two scans: COUNT(DISTINCT user_id) on 800M content_views rows (expensive, requires hash aggregation) and COUNT(*) on 5M users (cheap). The content_views scan dominates.

> **Interviewers Watch For**
>
> Whether the candidate uses the users table for the denominator (correct) vs content_views total rows (incorrect, as it counts views not users). The denominator must reflect all registered users.

> **Common Pitfall**
>
> Using `COUNT(*)` from content_views as the denominator would give views-per-view, not user penetration. Always think about what the denominator represents.

---

## Common follow-up questions

- How would you compute this per age bucket? _(Tests GROUP BY on users with a LEFT JOIN to content_views.)_
- What if some users in content_views are not in the users table? _(Tests whether orphaned views should be excluded from the numerator.)_
- How would you make this a time-series showing monthly penetration? _(Tests adding a date dimension and computing the metric per month.)_

## Related

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