Non-Draft Content

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

General Assembly

Problem

The publishing pipeline automatically appends '[DRAFT]' to titles that haven't been finalized. Find all content items whose title does not end with that suffix, since those are ready for syndication.

Everything except drafts.

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 one-line exclusion dressed up as a publishing workflow: keep every item except the ones whose title ends in the literal tag '[DRAFT]'. Nobody gets the projection wrong. What separates candidates is the anchor. The pattern has to say 'anything, then the tag, then the end of the string'. Drop the leading % or reach for = and the condition only matches a title that is exactly '[DRAFT]'. No row is, so both drafts on the seed ship to syndication.

Building it

Step 1: Anchor the tag to the end of title

'%[DRAFT]' reads as any prefix followed by the tag, with nothing after it. The % goes in front only. A trailing % would also catch a tag sitting mid-title, and the business says only a trailing tag marks a draft.

Step 2: Negate the match and keep the rest

NOT LIKE flips the test. On the seed it removes exactly two rows, 'Understanding Machine Learning [DRAFT]' among them, and leaves 198. If your count comes back as 200, your pattern never matched anything.

Step 3: Return the whole record

Syndication takes each item as-is, so every column comes back. SELECT * is the honest projection for a pass-through feed. Name the columns instead when a downstream contract pins the shape.

SELECT *
FROM content_items
WHERE title NOT LIKE '%[DRAFT]'

Common follow-up questions

• What changes if the pipeline starts writing '[draft]' in lower case? (Case sensitivity of `LIKE` differs by engine; tests normalizing with `UPPER(title)` before matching.)
• How would you still catch a title that ends in '[DRAFT]' followed by trailing spaces? (Tests trimming `title` before the suffix test.)
• If `title` could be `NULL`, would those rows come back? (`NULL NOT LIKE ...` is unknown, so the row is dropped; tests explicit `IS NULL` handling.)