Email Subject Lines Cut Off in the Inbox Preview
Most mobile inboxes show only about 45 characters of a subject line.
- Difficulty: Easy
- Topics: Strings, Basics
- Dialect: MySQL
- Problem: #276
Problem statement
Most mobile inboxes show only about 45 characters of a subject line. The CRM team wants every email campaign whose subject will be cut off, so it can be rewritten before the next send.
Measure each subject after trimming leading and trailing spaces. Return email_campaign_id, subject (as stored) and subject_length (the trimmed length in characters) for every campaign whose trimmed subject is longer than 45 characters. A NULL subject (an unfinished draft) is never in the answer. Order by subject_length descending, then email_campaign_id ascending.
Tables
Table: EmailCampaign
| Column | Type |
|---|---|
| email_campaign_id | int |
| subject | varchar |
| scheduled_on | date |
Primary key: email_campaign_id.
One row per email blast. subject can carry stray spaces pasted from the copy doc, or be NULL for a draft.
Examples
Example 1
EmailCampaign
| email_campaign_id | subject | scheduled_on |
|---|---|---|
| 1 | Your cart misses you | 2024-11-02 |
| 2 | Diwali deals end tonight: up to 60% off on everything you saved | 2024-10-30 |
| 3 | Free shipping this weekend only on all orders | 2024-11-08 |
| 4 | NULL | 2024-11-15 |
| 5 | Your monthly statement of reward points and exclusive member-only offers | 2024-11-01 |
| 6 | New arrivals for the monsoon are here, grab them! | 2024-07-04 |
Output
| email_campaign_id | subject | subject_length |
|---|---|---|
| 5 | Your monthly statement of reward points and exclusive member-only offers | 72 |
| 2 | Diwali deals end tonight: up to 60% off on everything you saved | 63 |
| 6 | New arrivals for the monsoon are here, grab them! | 49 |
How to solve Email Subject Lines Cut Off in the Inbox Preview
Compute the trimmed length once per row — CHAR_LENGTH(TRIM(subject)) — and keep the rows where it is above 45. Measuring the stored text would count the stray spaces at either end, so a short subject pasted with padding would be flagged wrongly, which is why the statement asks for the trimmed length.
A draft has a NULL subject; TRIM(NULL) and CHAR_LENGTH(NULL) are both NULL and NULL > 45 is unknown, so the row is filtered out without a separate check. A subject of exactly 45 characters fits, because the condition is strict.
The subjects here are plain ASCII, so LENGTH (bytes) and CHAR_LENGTH (characters) agree, as the alternative shows; with emoji or Devanagari they would not, and CHAR_LENGTH is the right one. Ordering by the alias works because ORDER BY runs after SELECT. The query is one scan.
Reference solution (MySQL)
SELECT email_campaign_id, subject, CHAR_LENGTH(TRIM(subject)) AS subject_length
FROM EmailCampaign
WHERE CHAR_LENGTH(TRIM(subject)) > 45
ORDER BY subject_length DESC, email_campaign_idAnother way
SELECT * FROM (SELECT email_campaign_id, subject, LENGTH(TRIM(subject)) AS subject_length FROM EmailCampaign WHERE subject IS NOT NULL) t WHERE subject_length > 45 ORDER BY subject_length DESC, email_campaign_id← Campaigns That Got Clicks but No Conversions · Email Campaigns That Drove Two or More Unsubscribes →