Views by Specific Users

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Problem

Only handles whose very first stored character is a lowercase 'a' belong to the spam wave, so a capitalized 'Alice' or a handle stored with a leading space is a different account. A trust-and-safety reviewer needs every row of content_views those accounts produced (alice, aaron42 and amelia among them), showing the view ID, content ID, timestamp and watch duration, oldest view first.

Retrieve all content views for a set of flagged user accounts

Tables

content_views

Columns: view_id INTEGER, content_id INTEGER, user_id INTEGER, device_id INTEGER, viewed_at TEXT, watch_seconds INTEGER

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

Expected output

Worked solution and explanation

What this really is

This is a key-translation problem in a trust-and-safety costume. The reviewer hands you usernames, but content_views never stores a username. It keys every view by the surrogate user_id, and the only place a handle turns into an id is the users dimension. Everyone can write the projection and the sort. What separates candidates is resolving the handles through users before touching the fact table, instead of reaching for a username column that is not there or guessing ids. Guess wrong and you hand the investigator someone else's viewing history while the flagged accounts walk.

Getting there

Step 1: Find where the handle lives

Scan the schema before writing anything: content_views has user_id but no username. The handle exists only in users, so users is the translation table and the filter has to run against it.

Step 2: Join users to content_views on user_id

An inner join on u.user_id = cv.user_id attaches each view to its account. Views with a NULL user_id drop out, which is right: an anonymous view cannot belong to a flagged account.

Step 3: Filter on the handle's first stored character

SUBSTR(u.username, 1, 1) IN ('a') keeps every account whose handle starts with a lowercase 'a' exactly as stored. That is the reviewer's rule: a handle like 'Alice' or one stored with a leading space is a different account, so do not reach for LOWER or TRIM here. Writing it as a membership test means a second flagged letter is one more value in the list, not a new OR branch.

Step 4: Project the view columns, oldest first

Return only view_id, content_id, viewed_at and watch_seconds, and sort by viewed_at ascending so the reviewer reads the trail as it happened. Nothing from users belongs in the output.

SELECT
    cv.view_id,
    cv.content_id,
    cv.viewed_at,
    cv.watch_seconds
FROM content_views AS cv
JOIN users AS u
    ON u.user_id = cv.user_id
WHERE SUBSTR(u.username, 1, 1) IN ('a')
ORDER BY cv.viewed_at;

Common follow-up questions

• How would you add the `username` to each row so the reviewer can tell accounts apart? (Project `u.username`; the join already carries it.)
• What if the flagged list were 1,000 exact handles instead of a prefix? (Load them into a table or CTE and join to it rather than writing a giant `IN` list.)
• What if the reviewer also wants anonymous views from the same devices? (Tests whether you see that `device_id` gives a second path to the data and that an inner join on `user_id` hides `NULL`-user views.)