LinkedIn: Open-to-Work Members With a Blank Headline
Recruiters skim the headline first, so the career-tools team wants to nudge members who switched on open to work but left their headline empty.
- Difficulty: Easy
- Topics: Basics, Strings
- Dialect: MySQL
- Problem: #724
Problem statement
Recruiters skim the headline first, so the career-tools team wants to nudge members who switched on open to work but left their headline empty.
Return member_id and full_name of every member with open_to_work = 1 whose headline is blank: NULL, the empty string, or nothing but spaces. Members not open to work never appear. Order the rows by member_id.
A practice scenario set at LinkedIn: the tables and data are invented, and this is not a question from, or endorsed by, LinkedIn.
Tables
Table: Member
| Column | Type |
|---|---|
| member_id | int |
| full_name | varchar |
| open_to_work | bool |
| headline | varchar |
Primary key: member_id.
open_to_work is 1 when the member shows the open-to-work frame; headline is the line under the name and may be NULL.
Examples
Example 1
Member
| member_id | full_name | open_to_work | headline |
|---|---|---|---|
| 1 | Aditi Rao | 1 | Data Analyst | SQL, Power BI |
| 2 | Kabir Khan | 1 | NULL |
| 3 | Sneha Iyer | 1 | |
| 4 | Rohan Das | 0 | NULL |
| 5 | Meera Nair | 1 | |
| 6 | Vikram Joshi | 1 | Aspiring Product Manager |
Output
| member_id | full_name |
|---|---|
| 2 | Kabir Khan |
| 3 | Sneha Iyer |
| 5 | Meera Nair |
How to solve LinkedIn: Open-to-Work Members With a Blank Headline
Three different values all mean "no headline": NULL, the empty string and a string of spaces. TRIM(headline) = '' catches the last two, because trimming a string of spaces leaves the empty string. It does not catch NULL — TRIM(NULL) is NULL and comparing NULL with anything is unknown — so NULL needs its own IS NULL test, joined with OR and wrapped in parentheses so that it binds before the open_to_work = 1 condition.
The first alternative folds NULL into the empty string up front with COALESCE(headline, '') and then measures the trimmed length; the second states the opposite condition (a headline with at least one visible character) and negates it. A headline with spaces around real words, like ' Aspiring Product Manager ', is not blank in any of them. The query is a single scan of Member.
Reference solution (MySQL)
SELECT member_id, full_name
FROM Member
WHERE open_to_work = 1
AND (headline IS NULL OR TRIM(headline) = '')
ORDER BY member_idAnother way
SELECT member_id, full_name FROM Member WHERE open_to_work = 1 AND CHAR_LENGTH(TRIM(COALESCE(headline, ''))) = 0 ORDER BY member_idAnother way
SELECT member_id, full_name FROM Member WHERE open_to_work = 1 AND NOT (headline IS NOT NULL AND LENGTH(TRIM(headline)) > 0) ORDER BY member_id← LinkedIn: Invitations Pending for More Than 30 Days · LinkedIn: Active Premium Members per Plan on Quarter End →