# Views by Specific Users

> Retrieve all content views for a set of flagged user accounts

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The trust and safety team flagged three accounts for review: alice, aaron42, and amelia. Pull every content view associated with those users. Show the view ID, content ID, timestamp, and watch duration, by view ID.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests a two-table join with an IN-list filter. Interviewers check whether you can filter by username (from one table) while returning data from another table.

---

### Break down the requirements

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

`JOIN users ON content_views.user_id = users.user_id` links views to usernames for filtering.

#### Step 2: Filter to flagged usernames

`WHERE username IN ('user002', 'user003', 'user004')` restricts to the three accounts under review.

#### Step 3: Return required columns ordered by view ID

Select `view_id`, `content_id`, `viewed_at`, and `watch_seconds`, ordered by `view_id`.

---

### The solution

**Username-filtered join with specific columns**

```sql
SELECT cv.view_id, cv.content_id, cv.viewed_at, cv.watch_seconds
FROM content_views cv
JOIN users u ON cv.user_id = u.user_id
WHERE u.username IN ('user002', 'user003', 'user004')
ORDER BY cv.view_id
```

> **Cost Analysis**
>
> The username filter narrows 3M users to 3 rows. The join then finds all views for those 3 user_ids from 300M rows. An index on `content_views(user_id)` makes the lookup fast.

> **Interviewers Watch For**
>
> Whether you filter on user_id (numeric, faster) or username (string, requires the join). Since the prompt gives usernames, the join is necessary.

> **Common Pitfall**
>
> Selecting columns from the users table that are not needed. The prompt asks for view data only (view_id, content_id, viewed_at, watch_seconds).

---

## Common follow-up questions

- How would you add the username to each row for context? _(Add u.username to the SELECT list.)_
- What if the user_id column in content_views has NULLs? _(INNER JOIN already excludes rows with NULL user_id. Use LEFT JOIN if you need to see anonymous views.)_
- What if you had 1,000 flagged users instead of 3? _(Use a temp table or CTE for the user list instead of an IN clause.)_

## Related

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