Content Type Distribution

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

We want a census of everything in our content library, untitled drafts included, so every row of content_items counts. How many items do we have of each content type, listed alphabetically?

How many of each content type?

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

This is a row census dressed up as an editorial report. Anyone can produce a count per type; what separates candidates is counting the right thing and handing the result back in the order asked for. content_items holds two drafts whose title is NULL, both of type 'short', so a query that counts titles reports 38 shorts instead of 40 and looks perfectly plausible doing it. Counting unique titles is worse still, because the same title repeats across many items.

Building it

Step 1: Set the grain to one row per content_type

The question is about types, not items, so the 200 rows collapse into one row per value of content_type. A single overall COUNT(*) answers a different question (200), and listing the types alone gives five names with nothing next to them.

Step 2: Count rows, not a column

COUNT(*) counts every row in the bucket. COUNT(title) counts only rows where title is filled, and the two untitled drafts vanish from 'short'. A census of the library includes drafts, so the count is of rows.

Step 3: Sort by the type name

Grouping hands rows back in whatever order the engine's buckets produce. The listing is alphabetical, so ORDER BY content_type is part of the answer, not presentation. Every type has 40 items here, so sorting by item_count would leave the order undetermined.

SELECT content_type, COUNT(*) AS item_count
FROM content_items
GROUP BY content_type
ORDER BY content_type

Counting rows

COUNT(*) gives 40 for every type, 'short' included: each item is a row, titled or not.

Counting titles

COUNT(title) skips the two drafts with a NULL title and reports 38 shorts. COUNT(DISTINCT title) drops further, because titles repeat across items.

Common follow-up questions

• How would you add each type's share of the library as a percentage? (Tests dividing each group's count by the overall total, for example with `SUM(COUNT(*)) OVER ()`, while avoiding integer division.)
• What if `content_type` could be NULL? (NULLs form their own group; tests whether to exclude them or label them 'unknown'.)
• How would you list only the types with more than 30 items? (Tests filtering groups on their count with `HAVING` rather than filtering rows with `WHERE`.)
• How would you leave the untitled drafts out of the census? (Tests turning the NULL `title` into an explicit filter instead of an accidental one.)