Customer Full Name Concat

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Amazon

Problem

An analyst keeps getting a differently ordered outreach roster from customers on every run, because plenty of customers share the same first and last name. Produce each customer's full name (first name, a space, last name) with their ID and country, alphabetical by full name and, among people with the same name, lowest customer ID first.

First name, last name. Combine them.

Tables

customers

Columns: customer_id INTEGER, first_name TEXT, last_name TEXT, country TEXT

Expected output

Worked solution and explanation

What this is really testing

This is a determinism problem dressed up as a mailing list. Anyone can glue first_name and last_name together. What separates candidates is noticing that the name is not a unique sort key. With 30k first names and 50k last names spread over 1.5M customers, names collide constantly: the preview already shows two 'Aiden Kim' rows. Sort by the name alone and every duplicate-name group comes back in whatever order the engine felt like that day, so two runs of the same query disagree.

Building it

Step 1: Build the name with an explicit space

Concatenate first_name || ' ' || last_name and alias it full_name. Forget the ' ' literal and every value reads 'AidenKim', which no outreach email should ever contain.

Step 2: Carry the ID and country alongside

Return customer_id and country next to the derived name. Both come straight off customers; nothing needs combining.

Step 3: Sort by name, then break ties on customer_id

ORDER BY full_name, customer_id sorts alphabetically, then settles every shared name by the lower account number. Because customer_id is unique, no further key can ever be consulted, so adding country to the sort would be dead weight.

SELECT
  first_name || ' ' || last_name AS full_name,
  customer_id,
  country
FROM customers
ORDER BY full_name, customer_id

Name only

ORDER BY full_name leaves the two 'Aiden Kim' rows in arbitrary order. The rows are right, the sequence is not reproducible.

Name, then ID

ORDER BY full_name, customer_id puts 42670 before 49879 on every run, because the second key is unique.

Common follow-up questions

• What happens to `full_name` if `last_name` can be NULL? (NULL propagation through `||` versus `CONCAT`.)
• How would you sort by last name first, then first name, while keeping the output stable? (Sorting on source columns instead of the derived string, still ending on `customer_id`.)
• Marketing wants only the first 100 rows. Does the tie-break still matter? (A cut through a tied group decides which customer is included.)