Titles Ending With S

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

Amazon

Problem

Pull all content items whose title ends with the letter 's', listed alphabetically by title so the catalog reads in order. For each item show the content ID, title, content type, duration in seconds, creator ID, and publish date, and when two share a title put the lower content ID first.

Naming conventions. Specifically the plurals.

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 tests

This is a suffix match dressed up as a catalog pull. Everyone reaches for LIKE; what separates candidates is where the wildcard goes. '%s%' asks whether an 's' appears anywhere, and on content_items that drags in 'Introduction to Cybersecurity' and 'Behind the Scenes of Product Management', so the 80-row answer quietly swells and still looks plausible. The second signal is what you ask before typing: is the match case-sensitive, and are the titles trimmed?

Break down the requirements

Step 1: Pick the suffix predicate

Filter content_items.title with LIKE '%s'. The leading % says any prefix, the literal s pins the final character.

Step 2: Nail case sensitivity

SQLite's LIKE is ASCII case-insensitive by default; Postgres LIKE is case-sensitive. Ask the interviewer which engine. Portable form: title LIKE '%s' OR title LIKE '%S', or normalize with LOWER(title) LIKE '%s'.

Step 3: Defend against trailing whitespace

'Articles ' LIKE '%s' is false because the last character is a space. If content_items.title is dirty, wrap in TRIM(title) LIKE '%s'. State the assumption either way.

Step 4: Project the asked columns and sort

Name content_id, title, content_type, duration_seconds, creator_id, publish_date explicitly rather than SELECT *. Then ORDER BY title, content_id: the catalog reads A to Z, and repeated titles such as 'Breaking Down DevOps' list the lower content_id first.

The solution

SELECT content_id,
       title,
       content_type,
       duration_seconds,
       creator_id,
       publish_date
FROM content_items
WHERE title LIKE '%s'
ORDER BY title, content_id;

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would you make this query case-insensitive in Postgres? (Probes ILIKE vs `LOWER(title) LIKE '%s'`, and the index implications of wrapping the column in a function (need an expression index).)
• The column has trailing whitespace on 12% of rows. How do you handle it? (Tests whether you reach for `TRIM(title) LIKE '%s'` at query time or push a cleanup upstream into the ingest job.)
• If `content_items` grew to 10 billion rows, how would you make suffix lookup fast? (Looks for a generated `last_char` column with an index, or a reversed-string column converting suffix search into prefix search.)
• How would you count titles ending in each letter a through z in one query? (Probes `GROUP BY SUBSTR(title, -1)` (or `RIGHT(title, 1)`) plus `LOWER` for case folding, in a single scan instead of 26 LIKE filters.)