# Titles Ending With S

> Naming conventions. Specifically the plurals.

Canonical URL: <https://datadriven.io/problems/titles_ending_with_s>

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

Pull all content items whose title ends with the letter 's'. For each item, show the content ID, title, content type, duration in seconds, creator ID, and publish date.

## Worked solution and explanation

### Why this problem exists in real interviews

Trivial filter, real signal. The interviewer is checking whether you reflexively ask `case-sensitive?` and `is the data trimmed?` before touching the keyboard. On `content_items.title` (3M rows, free-form strings), `LIKE '%s'` is the answer; the score comes from the assumptions you state out loud first.

---

### 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

Return `content_id, title, content_type, duration_seconds, creator_id, publish_date`. `ORDER BY title` for deterministic output the grader can diff.

---

### The solution

**SUFFIX FILTER**

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

> **Cost Analysis**
>
> A leading-wildcard `'%s'` cannot use a B-tree index on `title`; the planner full-scans `content_items`. At 3M rows that is a sub-second scan, fine. At billions, you would materialize a `last_char` generated column and index that, or store `REVERSE(title)` and switch to a prefix LIKE.

> **Interviewers Watch For**
>
> Ask two things before writing: case-sensitive match, and whether `title` may have trailing whitespace. Also flag NULL: `NULL LIKE '%s'` is NULL so those rows drop, which is almost always what you want. Pick your answer and write.

> **Common Pitfall**
>
> Writing `LIKE '%s%'` (matches any title containing `s`) or `LIKE 's%'` (prefix, not suffix). The wildcard goes on the side opposite the anchor. Also, `RIGHT(title, 1) = 's'` works but breaks the moment the column has a trailing space, same as LIKE.

---

### 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/titles_ending_with_s)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.