What Fills the Feed

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

Forbes

Problem

Find how many published items each content type has in the content_items catalog, most to least, for an editor planning next quarter's production budget. An item with no publish date has not gone out to the feed yet, so it stays out of the tally.

Every slot in the catalog is a bet. See which format keeps winning it.

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 is really asking

This is a question about what a count measures, dressed up as a format breakdown. Every content type holds exactly 40 rows in content_items, so COUNT(*) ties all five and the standings claim the feed is split evenly. The feed only shows what went out, and 12 video items have no publish_date. Count the column, not the row, and video drops to 28 and falls to the bottom. Miss it and the editor budgets for a tie that does not exist.

Step 1: Decide what one published item looks like

A row of content_items is a catalog entry, not a published item: drafts and unscheduled pieces sit in the same table with publish_date empty. COUNT(publish_date) skips those NULLs, which is exactly the definition the question gives.

Step 2: Tally at the content_type grain

GROUP BY content_type gives one row per format. Every type survives because each has published items; video lands at 28, the other four at 40.

Step 3: Sort by the tally, largest first

ORDER BY published_items DESC puts the four full formats on top and video last. The four types tied at 40 may come back in any order: equal volume gives no reason to prefer one, so no tiebreaker is owed.

SELECT content_type, COUNT(publish_date) AS published_items
FROM content_items
GROUP BY content_type
ORDER BY published_items DESC

`COUNT(*)`

Counts every catalog row. All five types come back at 40, video's 12 unpublished items included, and the standings show a five-way tie.

`COUNT(publish_date)`

Counts only rows with a publish_date. Video drops to 28 and sits last; the other four stay at 40.

Follow-up questions

Common follow-up questions

• Add each type's total catalog size next to its published count. (Tests whether they keep `COUNT(*)` and `COUNT(publish_date)` side by side in one pass.)
• Restrict the tally to items published in the last 12 months. (Adds a `publish_date` predicate; the NULL rows now drop out through the comparison itself.)
• How would you serve this to a dashboard that refreshes every 30 seconds? (Pushes toward a maintained rollup instead of a full scan per load.)