Airbnb: Host Display Names With a Last Initial

Easy
555.

Airbnb: Host Display Names With a Last Initial

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

ColumnType
host_idint
first_namevarchar
last_namevarchar

Primary key: host_id.

first_name is never NULL; last_name may be NULL but is never an empty string.

Example

The rows must come back in the order shown.

Host

host_idfirst_namelast_name
1MeeraIyer
2JohnNULL
3AaravSharma
4EmmaBose
5ZaraNULL

Output

host_iddisplay_name
1Meera I.
2John
3Aarav S.
4Emma B.
5Zara
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.

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

ColumnType
host_idint
first_namevarchar
last_namevarchar

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_idfirst_namelast_name
1MeeraIyer
2JohnNULL
3AaravSharma
4EmmaBose
5ZaraNULL

Output

host_iddisplay_name
1Meera I.
2John
3Aarav S.
4Emma B.
5Zara

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_id

Another way

SELECT host_id, CONCAT_WS(' ', first_name, CONCAT(SUBSTRING(last_name, 1, 1), '.')) AS display_name FROM Host ORDER BY host_id

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