Top-Ranked Wines by Variety

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

The product team is looking at content that runs shorter than user 197's typical session. Take user 197's average session length, then return every content item whose duration is at or below that average, walking through them from the lowest content ID up to the highest. For each row, show the literal user ID 197, the content ID, and the content's duration in seconds.

The best bottles. Ranked by variety.

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

One number, two tables that never meet

This is a threshold lookup dressed up as a product question. The skill being probed: can you compute one number from user_sessions and use it to filter content_items, a table it has no relationship to? Anyone can write AVG(...). The trap is reaching for a JOIN because two tables are on the screen. They share no key, so the join either fails or fans out, and each content item gets compared once per session instead of once against the average.

Step 1: Compute the threshold as a scalar subquery

SELECT AVG(session_duration_sec) FROM user_sessions WHERE user_id = 197 returns exactly one row and one column. That is what lets it sit on the right side of a comparison. Drop the WHERE and you get the global average, a plausible number that quietly returns the wrong set of content.

Step 2: Filter content_items against it

Put the subquery inside WHERE ci.duration_seconds <= (...). The statement says "at or below", so it is <=, not <. The engine evaluates the uncorrelated subquery once, then scans content_items once.

Step 3: Emit 197 as a literal

user_id does not exist on content_items, and you are not joining to get it. 197 AS user_id stamps the constant on every row, which is exactly what the output asks for.

Step 4: Order by content_id

"Lowest ID up to the highest" is ORDER BY ci.content_id, ascending. The comparison is order sensitive, so this is not optional.

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
)
ORDER BY ci.content_id

The join instinct

A cross join of content_items and user_sessions pairs every content row with every session of user 197. A content item can match several sessions and come back several times, and the cutoff becomes each individual session instead of the average.

The scalar subquery

The subquery collapses user 197's sessions into one number first. Each content row is compared against that single value and appears at most once.

SELECT 197 AS user_id, ci.content_id, ci.duration_seconds
FROM content_items ci
CROSS JOIN user_sessions us
WHERE us.user_id = 197
  AND ci.duration_seconds <= us.session_duration_sec
ORDER BY ci.content_id

Common follow-up questions

• How would you return this for every user at once? (Needs a per-user average, so the subquery becomes correlated or a grouped CTE, and a join on `user_id` finally appears.)
• What changes if you want content strictly shorter than the average, with `NULL` durations kept in the output? (Tests `<` versus `<=` and explicit `NULL` handling with `COALESCE` or `IS NULL`.)