# The Ones Who Hold Attention > Time on screen is the real vote. Find the creators earning it. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem The content team is studying which creators hold viewer attention. A session counts toward a creator if the user in that session is also a creator in the content table. For each such creator, show the creator's ID and their average session length in seconds, sorted by creator ID. ## Worked solution and explanation Strip the costume off this one. It reads like an attention-analytics question, but the real probe is whether you notice that user_id and creator_id live in the same ID space. There is no person column linking the two tables. The only thing that says a session belongs to a creator is that the session's user_id equals some content row's creator_id. The skill being tested: can you express that semantic overlap as the join key, instead of inventing a relationship the schema does not give you? ### The trap Most candidates stall looking for a foreign key that connects sessions to content. There isn't one. session_id, device_id, and content_id are all decoys. The team's rule is the join condition stated in plain words: a session counts only when its user_id appears as a creator_id. Translate that one sentence into ON us.user_id = ci.creator_id and the inner join does the filtering for you. Reach instead for a WHERE user_id IN (SELECT creator_id ...) and you get the same rows but you have rebuilt a join the long way, and you still have to bolt on the grouping. #### Step 1: Join sessions to creators on the shared ID Put user_sessions on the left and inner-join content_items on us.user_id = ci.creator_id. The inner part is doing double duty: it pairs each session with the creator it belongs to AND drops every session whose user never created anything. That dropped set is exactly what the business rule wanted gone. #### Step 2: Collapse to one row per creator and average Group the joined rows by ci.creator_id and take AVG(us.session_duration_sec). One creator can map to many content rows and many sessions, so you are averaging the session durations within each creator's bucket, not per content item. #### Step 3: Order by creator ID The team wants the list walkable by ID, so sort ascending on creator_id. It is presentation, not logic, but skipping it leaves engine-dependent ordering that will not match the expected preview. **Average session length per creator** ```sql SELECT ci.creator_id, AVG(us.session_duration_sec) AS avg_session_duration FROM user_sessions us INNER JOIN content_items ci ON us.user_id = ci.creator_id GROUP BY ci.creator_id ORDER BY ci.creator_id; ``` *The join key IS the filter. Group, average, sort.* > **Trick to solving** > > The whole problem turns on one realization: the relationship between the tables is value equality on two differently named columns, not a foreign key. Once you write ON us.user_id = ci.creator_id, the inner join silently enforces 'user is also a creator' and you never need a separate WHERE. > **Common pitfall** > > Fan-out panic. A creator with three content rows makes each of their sessions appear three times after the join, so people scramble to dedup. For AVG it does not matter: every session for that creator is duplicated by the SAME factor, so the sum and the count scale together and the average is unchanged. But note the asymmetry: if the ask were total watch time (SUM) or session COUNT, that triple-counting WOULD corrupt the answer and you would need to pre-aggregate sessions before joining. **Inner join on the shared ID** ON us.user_id = ci.creator_id does the membership test and the pairing in one pass; the planner hashes content_items once and probes it. Reads as the relationship the data actually has. **WHERE user_id IN (subquery)** Filtering sessions by a creator_id subquery returns the same creators but rebuilds the join semantics by hand, runs a second scan of content_items, and still needs its own GROUP BY. More moving parts, no upside here. > **Interviewers watch for** > > Whether you say out loud that user_id and creator_id are the same identifier wearing two names. Candidates who name that overlap before writing SQL signal they read the schema, not just the prompt. The ones who hunt for a missing join table or ask 'where is the link?' reveal they are pattern-matching on column names. > **In production** > > This shape is everywhere once an identity is reused across roles: a marketplace user who is also a seller, an author who is also a reader, an employee who is also a customer. The warehouse rarely gives you a clean bridge table for it; you join on the shared natural key and accept the fan-out math. Knowing when fan-out is harmless (AVG) versus poison (SUM or COUNT) is the senior instinct. ## Common follow-up questions - Now give total session time per creator instead of the average. _(Forces them to confront the fan-out they got away with for AVG: they must pre-aggregate sessions per user before joining, or the SUM triple-counts.)_ - Include creators who published content but never had a matching session, showing them with a null or zero average. _(Pushes from inner join to left join with content_items on the left, and tests COALESCE and null-handling on the aggregate.)_ - Restrict to sessions from this year only, and explain where the filter goes. _(Tests whether they put the date predicate in WHERE before aggregation and understand it changes which sessions feed the average.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_ones_who_hold_attention) - [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.