High-Output Creators

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

L4

Asked in

Airbnb

Problem

Which creators have published at least an hour of content in total (3,600 seconds), and what is each one's average duration per item, from the longest average down to the shortest? Items with no creator on record pool together as a single unattributed entry that is held to the same hour bar.

High engagement creators.

Tables

content_items

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

Expected output

Worked solution and explanation

Why this problem exists in real interviews

Two aggregates over the same group, used for different purposes: SUM gates who qualifies (an hour of total runtime) and AVG shapes the output and the sort. Mixing the two up, filtering on AVG or thresholding each item in WHERE, is the failure mode. The HAVING clause is the natural home for the gate.

Break down the requirements

Step 1: Group by creator_id

Every metric lives at the creator grain. GROUP BY creator_id is the only sensible key because content_items is one row per piece of content, not per creator. Rows with a NULL creator_id fall into one group of their own, which is exactly the unattributed entry the question asks for.

Step 2: Gate on total runtime, not per-item

HAVING SUM(duration_seconds) >= 3600 filters the groups. Putting the threshold in WHERE would filter individual items, which is not what an hour of total content means.

Step 3: Project the average and sort

AVG(duration_seconds) is independent of the gate. ORDER BY avg_duration DESC puts the long-form creators at the top, which is what an editorial team would scan first. Creators tied on the average can come in either order.

The solution

SELECT creator_id,
       AVG(duration_seconds) AS avg_duration
FROM content_items
GROUP BY creator_id
HAVING SUM(duration_seconds) >= 3600
ORDER BY avg_duration DESC

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• Restrict this to creators whose first publish was in the last 12 months. (Tests whether you add a `MIN(publish_date)` condition to `HAVING` or filter inside a subquery.)
• Return the median duration per creator instead of the average. (Probes knowledge of `PERCENTILE_CONT` or `PERCENTILE_DISC` and how engines differ on syntax.)
• Now break the average out by `content_type` within each qualifying creator. (Checks comfort with multi-key grouping and whether the hour threshold stays at the creator level or moves to the type level.)