Engagement by Content Type

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

Yelp

Problem

Articles have no recorded running time in content_items, yet we still list them alongside the timed formats. For each content type, give its total duration in seconds and how many items it has, longest total first.

Some content types get all the attention.

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

What this really is

Strip the costume and this is a NULL-preservation problem dressed up as a format report. Grouping by content_type and summing is the easy 80 percent. The real test is the article format: every one of its 40 rows has a NULL duration_seconds, so a bare SUM returns NULL and COUNT(duration_seconds) counts none of them. Miss that and articles either vanish behind a filter, sit at NULL, or report zero items. They should read total 0 with their full count of 40.

Working through it

Step 1: Collapse content_items to one row per content_type

Five formats means five output rows. Resist any WHERE duration_seconds IS NOT NULL: it looks like hygiene, and it deletes the article format outright.

Step 2: Default the sum, not the rows

SUM skips NULLs, which is fine for the timed formats. For a group where every value is NULL, though, it returns NULL instead of 0. Wrap it as COALESCE(SUM(duration_seconds), 0) so articles land on a real zero.

Step 3: Count rows, then sort by the total

COUNT(*) counts items; COUNT(duration_seconds) counts durations, which gives articles 0. Then sort by total_duration descending. The five totals all differ, so the order is fully decided without any tie rule.

The solution

SELECT content_type,
       COALESCE(SUM(duration_seconds), 0) AS total_duration,
       COUNT(*) AS item_count
FROM content_items
GROUP BY content_type
ORDER BY total_duration DESC

Bare aggregates

SUM(duration_seconds) and COUNT(duration_seconds) leave the article row as NULL and 0. That reads as a format nobody published.

Defaulted sum, counted rows

COALESCE(SUM(duration_seconds), 0) and COUNT(*) give the article row 0 and 40: published, just never timed.

Common follow-up questions

• If we also report average duration per item, what should articles show? (`AVG` ignores NULLs, so the choice between NULL and 0 becomes a business decision.)
• `publish_date` is missing on some items: where do they go when you add a year dimension? (Tests NULL handling in the grouping key, not just in the measure.)
• How would you show a format that has no items at all? (Needs a list of formats to outer-join from, since `content_items` cannot produce a group with no rows.)