Session-Fit Content

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

Asked in

Amazon

Problem

For user 197, find content items whose duration is less than or equal to that user's average session length. Show the user ID, content ID, and content duration.

Content that fits the session length.

Tables

content_items

Columns: content_id INTEGER, title TEXT, content_type TEXT, duration_seconds INTEGER, creator_id INTEGER, publish_date TEXT

user_sessions

Columns: session_id INTEGER, user_id INTEGER, device_id INTEGER, session_start TEXT, session_duration_sec INTEGER, pages_viewed INTEGER

Expected output

Worked solution and explanation

Why this problem exists in real interviews

Drawn from a session analysis domain, this question centers on filtering and projection over the content_items and user_sessions tables. The tricky part is handling columns like content_id, duration_seconds, and user_id correctly under the given constraints.

Break down the requirements

Step 1: Apply the range filter

The WHERE clause restricts rows to the target range. Applying this filter early reduces the volume flowing into downstream operations.

Step 2: Use a subquery to find the reference value

The scalar subquery computes a single value (like the maximum) that the outer query filters against. This avoids a self-join.

The solution

SELECT 197 AS user_id, ci.content_id, ci.duration_seconds
FROM content_items ci
WHERE ci.duration_seconds <= (
    SELECT AVG(session_duration_sec)
    FROM user_sessions
    WHERE user_id = 197
)

Common follow-up questions

• What result would you get if every value in `content_items.duration_seconds` were NULL? Would your query return an empty set or something unexpected? (Tests extreme NULL scenarios and whether the candidate guards against edge cases in `duration_seconds`.)
• `user_sessions.device_id` has roughly 6,000,000 distinct values. What index strategy would you use to avoid a full scan on `user_sessions`? (Tests indexing knowledge specific to the high-cardinality `device_id` column in `user_sessions`.)
• `content_items.content_type` only has 10 distinct values. If a new category were added, would your query automatically include it? (Tests whether the query hard-codes values or dynamically adapts to `content_type` changes.)