Airbnb: Host Display Names With a Last Initial — SQL Problem
For privacy, a listing page shows the host by first name and last initial only. The profile team is backfilling that display name for every host account.
- Difficulty: Easy
- Topics: Strings, Conditional Logic
- Dialect: MySQL
- Problem: #555
Problem statement
For privacy, a listing page shows the host by first name and last initial only. The profile team is backfilling that display name for every host account.
Return host_id and display_name: the first name, a space, the first letter of the last name and a full stop — Meera I. for Meera Iyer. A host with no last name on file (last_name NULL) is shown by first name alone. Return the rows ordered by host_id.
A practice scenario set at Airbnb: the tables and data are invented, and this is not a question from, or endorsed by, Airbnb.
Tables
Table: Host
| Column | Type |
|---|---|
| host_id | int |
| first_name | varchar |
| last_name | varchar |
Primary key: host_id.
first_name is never NULL; last_name may be NULL but is never an empty string.
Examples
Example 1
Host
| host_id | first_name | last_name |
|---|---|---|
| 1 | Meera | Iyer |
| 2 | John | NULL |
| 3 | Aarav | Sharma |
| 4 | Emma | Bose |
| 5 | Zara | NULL |
Output
| host_id | display_name |
|---|---|
| 1 | Meera I. |
| 2 | John |
| 3 | Aarav S. |
| 4 | Emma B. |
| 5 | Zara |
How to solve Airbnb: Host Display Names With a Last Initial
The display name is built from three pieces: the first name, a space, and the last name's first letter followed by a full stop. LEFT(last_name, 1) takes the letter and CONCAT glues the pieces together.
The NULL last name decides the shape of the query. MySQL's CONCAT returns NULL if any argument is NULL, so the plain expression would give hosts without a last name no display name at all. The solution branches with CASE; one alternative lets the NULL happen and catches it with COALESCE(…, first_name).
The neatest alternative uses CONCAT_WS, which joins its arguments with a separator and skips NULL arguments — the inner CONCAT(…, '.') is NULL when there is no last name, so CONCAT_WS returns just the first name with no stray space. The order is fixed by host_id. One scan of the table.
Reference solution (MySQL)
SELECT host_id,
CASE WHEN last_name IS NULL THEN first_name
ELSE CONCAT(first_name, ' ', LEFT(last_name, 1), '.') END AS display_name
FROM Host
ORDER BY host_idAnother way
SELECT host_id, CONCAT_WS(' ', first_name, CONCAT(SUBSTRING(last_name, 1, 1), '.')) AS display_name FROM Host ORDER BY host_idAnother way
SELECT host_id, COALESCE(CONCAT(first_name, ' ', LEFT(last_name, 1), '.'), first_name) AS display_name FROM Host ORDER BY host_id← Airbnb: Host Payout for Each Completed Stay · Airbnb: Hosts Meeting a Simplified Superhost Bar →