Truncated Push Notifications

A gym's mobile app shows push notifications on the lock screen, but the preview only fits 50 characters. Anything longer gets cut off, so the marketing team wants to find and rewrite those messages.

Return notification_id and body_length (the number of characters in body) for every notification whose body is longer than 50 characters. A body of exactly 50 characters fits and must not be returned. Count characters, not bytes: an accented letter such as é counts as one. Rows may be in any order; return no rows if every message fits.

Tables

push_notifications — One row per push notification drafted for the gym app.

Example

push_notifications

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Select & Filter · problem 6 of 70 · SQLite

The idea

LENGTH(body) returns how many characters a text value has, so the filter is WHERE LENGTH(body) > 50, and the same expression in the SELECT list, aliased as body_length, produces the second column. Strict > matters: a 50-character message fits the preview exactly.

For text, SQLite counts characters. Letters such as é or ñ take two bytes in UTF-8, so a byte count, for example LENGTH(CAST(body AS BLOB)), overstates their length. Character count is what the lock screen cares about.

The alternative avoids counting altogether: SUBSTR(body, 51) is everything from the 51st character on. It is non-empty exactly when the body has more than 50 characters. This trick is useful when you only need to know whether a string passes a limit.

Solution

SELECT notification_id,
       LENGTH(body) AS body_length
FROM push_notifications
WHERE LENGTH(body) > 50;

Another way

SELECT notification_id,
       LENGTH(body) AS body_length
FROM push_notifications
WHERE SUBSTR(body, 51) <> '';

Why common answers fail

Uses >= 50, so messages of exactly 50 characters, which do fit, are flagged too.

SELECT notification_id, LENGTH(body) AS body_length FROM push_notifications WHERE LENGTH(body) >= 50;

Measures bytes instead of characters, so text with accented letters looks longer than it is.

SELECT notification_id, LENGTH(CAST(body AS BLOB)) AS body_length FROM push_notifications WHERE LENGTH(CAST(body AS BLOB)) > 50;

Compares the text itself with the string 50 alphabetically instead of comparing its length.

SELECT notification_id, LENGTH(body) AS body_length FROM push_notifications WHERE body > '50';

What the interviewer is checking

Checks basic string functions, strict versus inclusive boundaries, and awareness of characters versus bytes.

Mistakes to avoid

• Using >= when the limit itself is allowed.
• Counting bytes instead of characters for non-English text.
• Comparing the text to a number instead of its length.

Follow-ups to expect

• Show the 50-character preview the user actually sees. (SUBSTR(body, 1, 50))
• Find the longest message per category. (MAX(LENGTH(body)) with GROUP BY category)
• Ignore leading and trailing spaces when measuring. (LENGTH(TRIM(body)))

In real work

Apps validate SMS, push, meta-description and ad-copy lengths against display limits before content goes live.

Practice next