# Read the Manual

> Some titles promise to walk you through it. Count the ones that say so out loud.

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

Domain: SQL · Difficulty: easy · Seniority: L3 · Asked in: Yelp

## Problem

The content team wants to know how much of the catalog is explicitly framed as a "how-to" walkthrough. Count how many content items have the word 'how' somewhere in their title (case-insensitive).

## Tables

### content_items

- content_id INTEGER
- title TEXT
- content_type TEXT
- duration_seconds INTEGER
- creator_id INTEGER
- publish_date TEXT

Sample rows (10 of 200):

| content_id | title | content_type | duration_seconds | creator_id | publish_date |
| --- | --- | --- | --- | --- | --- |
| 243 | The Ultimate Guide to AI Trends | article | NULL | 197 | 2026-02-02 |
| 286 | Top 10 Mobile Apps | podcast | 154 | 294 | 2025-03-03 |
| 329 | Understanding Machine Learning | short | 201 | 391 | 2026-04-04 |
| 372 | Mastering Tech Startups | livestream | 248 | 488 | 2025-05-05 |
| 415 | Introduction to Cybersecurity | video | 295 | 585 | 2026-06-06 |
| 458 | Deep Dive: Cloud Computing | article | NULL | 682 | 2025-07-07 |
| 501 | Behind the Scenes of Product Management | podcast | 389 | 779 | 2026-08-08 |
| 544 | Breaking Down DevOps | short | 436 | 876 | 2025-09-09 |
| 587 | Exploring Web Development | livestream | 483 | NULL | 2026-10-10 |
| 630 | How to Data Science | video | 530 | 1070 | 2025-11-11 |

## Expected output

First 1 of 1 rows:

| howto_count |
| --- |
| 20 |

## Worked solution and explanation

### It looks trivial. Two ways to quietly get it wrong.

Strip the costume and this is a case-insensitive substring count, and the whole game is what 'contains how' actually means. There are two quiet ways to blow it. Match the standalone word (or worse, `title = 'how'`) and you drop every 'How to Data Science' the content team is actually asking about. Match the substring but forget to fold case, and a bare `LIKE '%how%'` silently skips every capitalized 'How', which is most of them. The count only lands when you treat 'how' as any run of h-o-w, anywhere in the title, regardless of case. Nobody sweats this on a whiteboard; the tell of a junior answer is one that assumes the titles are clean and single-cased.

---

### Break down the requirements

#### Step 1: Match titles containing 'how'

`WHERE LOWER(title) LIKE '%how%'` finds all content items with 'how' anywhere in the title, regardless of case. The leading and trailing `%` are what make it a substring test rather than an exact match, and LOWER() is what keeps 'How' from slipping through.

#### Step 2: Count the matches

`COUNT(*)` with no GROUP BY collapses the matches to a single number, which is exactly the one-row, one-column shape the content team wants.

---

### The solution

**Case-insensitive pattern match with count**

```sql
SELECT COUNT(*) AS howto_count
FROM content_items
WHERE LOWER(title) LIKE '%how%'
```

> **Cost Analysis**
>
> Full scan of 3M rows with a LIKE pattern. The leading `%` prevents index usage, and wrapping `title` in LOWER() also blocks any plain index on the column. For frequent queries, a full-text index or a computed column storing LOWER(title) would help.

> **Interviewers Watch For**
>
> Whether the candidate handles case at all, and whether they do it portably. SQLite's LIKE is case-insensitive for ASCII by default, so a bare `LIKE '%how%'` passes here and hides the bug; the same query undercounts on PostgreSQL, where LIKE is case-sensitive. Wrapping the column in LOWER() is the answer that survives the move to production.

> **Common Pitfall**
>
> Using `= 'how'` instead of `LIKE '%how%'`. The exact match would miss titles like 'How to Write SQL Joins' or 'A guide on how to optimize queries'.

---

## Common follow-up questions

- What if you needed the count per content_type? _(Add GROUP BY content_type to break down how-to titles by format.)_
- How would you handle titles with HTML entities like '&amp;how'? _(Tests data cleaning awareness before pattern matching.)_
- What if the search needed to match only the whole word 'how' and not substrings like 'showcase'? _(The LIKE '%how%' pattern matches 'how' as a substring, so it also catches words like 'showcase' or 'somehow' that contain the letters; matching the standalone word would need word-boundary handling.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/tutorial_content_count)
- [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.