LinkedIn: Open-to-Work Members With a Blank Headline

Easy
724.

LinkedIn: Open-to-Work Members With a Blank Headline

EasyBasicsStrings

MySQL

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

ColumnType
member_idint
full_namevarchar
open_to_workbool
headlinevarchar

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.

Example

The rows must come back in the order shown.

Member

member_idfull_nameopen_to_workheadline
1Aditi Rao1Data Analyst | SQL, Power BI
2Kabir Khan1NULL
3Sneha Iyer1
4Rohan Das0NULL
5Meera Nair1
6Vikram Joshi1 Aspiring Product Manager

Output

member_idfull_name
2Kabir Khan
3Sneha Iyer
5Meera Nair
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.

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

ColumnType
member_idint
full_namevarchar
open_to_workbool
headlinevarchar

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_idfull_nameopen_to_workheadline
1Aditi Rao1Data Analyst | SQL, Power BI
2Kabir Khan1NULL
3Sneha Iyer1
4Rohan Das0NULL
5Meera Nair1
6Vikram Joshi1 Aspiring Product Manager

Output

member_idfull_name
2Kabir Khan
3Sneha Iyer
5Meera 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_id

Another way

SELECT member_id, full_name FROM Member WHERE open_to_work = 1 AND CHAR_LENGTH(TRIM(COALESCE(headline, ''))) = 0 ORDER BY member_id

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