Work E-mail Addresses for New Joiners

Easy
226.

Work E-mail Addresses for New Joiners

IT creates a mailbox for each new joiner before day one. The address is the first name, a dot and the last name, all in lower case with any spaces removed, followed by @acmecorp.in — Ravi De Souza becomes ravi.desouza@acmecorp.in. Some joiners have a single name and last_name NULL; their address is just the first name, @acmecorp.in (no dot).

Return joiner_id and work_email for every joiner. Return the rows in any order.

Tables

Table: NewJoiner

ColumnType
joiner_idint
first_namevarchar
last_namevarchar
joining_datedate

Primary key: joiner_id.

One row per person with an accepted offer. last_name is NULL for people who go by one name; a last name may contain a space.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

NewJoiner

joiner_idfirst_namelast_namejoining_date
1RaviDe Souza2025-07-01
2PriyaIyer2025-07-01
3AnbuNULL2025-07-07
4SaraVan Der Berg2025-07-14
5HarshMehta2025-07-14

Output

joiner_idwork_email
1ravi.desouza@acmecorp.in
2priya.iyer@acmecorp.in
3anbu@acmecorp.in
4sara.vanderberg@acmecorp.in
5harsh.mehta@acmecorp.in
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.

Work E-mail Addresses for New Joiners — SQL Strings Problem

IT creates a mailbox for each new joiner before day one. The address is the first name, a dot and the last name, all in lower case with any spaces removed,…

  • Difficulty: Easy
  • Topics: Strings, Conditional Logic
  • Dialect: MySQL
  • Problem: #226

Problem statement

IT creates a mailbox for each new joiner before day one. The address is the first name, a dot and the last name, all in lower case with any spaces removed, followed by @acmecorp.in — Ravi De Souza becomes ravi.desouza@acmecorp.in. Some joiners have a single name and last_name NULL; their address is just the first name, @acmecorp.in (no dot).

Return joiner_id and work_email for every joiner. Return the rows in any order.

Tables

Table: NewJoiner

ColumnType
joiner_idint
first_namevarchar
last_namevarchar
joining_datedate

Primary key: joiner_id.

One row per person with an accepted offer. last_name is NULL for people who go by one name; a last name may contain a space.

Examples

Example 1

NewJoiner

joiner_idfirst_namelast_namejoining_date
1RaviDe Souza2025-07-01
2PriyaIyer2025-07-01
3AnbuNULL2025-07-07
4SaraVan Der Berg2025-07-14
5HarshMehta2025-07-14

Output

joiner_idwork_email
1ravi.desouza@acmecorp.in
2priya.iyer@acmecorp.in
3anbu@acmecorp.in
4sara.vanderberg@acmecorp.in
5harsh.mehta@acmecorp.in

How to solve Work E-mail Addresses for New Joiners

Building the address is string plumbing: LOWER(first_name), a dot, LOWER(REPLACE(last_name, ' ', '')) and the domain, joined with CONCAT.

The NULL last name is the catch. In MySQL CONCAT returns NULL as soon as any argument is NULL, so the naive expression gives a single-name joiner no address at all. The solution branches with CASE WHEN last_name IS NULL, leaving out both the dot and the last name in that branch.

Two neater forms avoid the branch. CONCAT_WS('.', first_name, last_name_without_spaces) joins its non-NULL arguments with the separator and simply skips a NULL — with no stray dot — and the result is lower-cased once. Or build the '.' + last name piece with CONCAT, which is NULL exactly when the last name is, and turn it into an empty string with IFNULL. Each is one pass over the table with constant work per row.

Reference solution (MySQL)

SELECT joiner_id,
       CASE WHEN last_name IS NULL
            THEN CONCAT(LOWER(first_name), '@acmecorp.in')
            ELSE CONCAT(LOWER(first_name), '.', LOWER(REPLACE(last_name, ' ', '')), '@acmecorp.in')
       END AS work_email
FROM NewJoiner

Another way

SELECT joiner_id, CONCAT(LOWER(CONCAT_WS('.', first_name, REPLACE(last_name, ' ', ''))), '@acmecorp.in') AS work_email FROM NewJoiner

Another way

SELECT joiner_id, CONCAT(LOWER(first_name), IFNULL(CONCAT('.', LOWER(REPLACE(last_name, ' ', ''))), ''), '@acmecorp.in') AS work_email FROM NewJoiner

← Employees Without a Reachable Emergency Contact · Late Punch-Ins per Employee in March 2025 →