Email Subject Lines Cut Off in the Inbox Preview

Easy
276.

Email Subject Lines Cut Off in the Inbox Preview

EasyStringsBasics

MySQL

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

ColumnType
email_campaign_idint
subjectvarchar
scheduled_ondate

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.

Example

The rows must come back in the order shown.

EmailCampaign

email_campaign_idsubjectscheduled_on
1Your cart misses you2024-11-02
2Diwali deals end tonight: up to 60% off on everything you saved2024-10-30
3 Free shipping this weekend only on all orders 2024-11-08
4NULL2024-11-15
5Your monthly statement of reward points and exclusive member-only offers2024-11-01
6New arrivals for the monsoon are here, grab them!2024-07-04

Output

email_campaign_idsubjectsubject_length
5Your monthly statement of reward points and exclusive member-only offers72
2Diwali deals end tonight: up to 60% off on everything you saved63
6New arrivals for the monsoon are here, grab them!49
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
email_campaign_idint
subjectvarchar
scheduled_ondate

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_idsubjectscheduled_on
1Your cart misses you2024-11-02
2Diwali deals end tonight: up to 60% off on everything you saved2024-10-30
3 Free shipping this weekend only on all orders 2024-11-08
4NULL2024-11-15
5Your monthly statement of reward points and exclusive member-only offers2024-11-01
6New arrivals for the monsoon are here, grab them!2024-07-04

Output

email_campaign_idsubjectsubject_length
5Your monthly statement of reward points and exclusive member-only offers72
2Diwali deals end tonight: up to 60% off on everything you saved63
6New 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_id

Another 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 →