LeetCode 2504 - Concatenate the Name and the Profession

The problem asks us to transform data in a SQL table named Person. Each row represents a person, with three columns: personid, name, and profession.

leetcodeeasydatabase

LeetCode Problem 2504

Difficulty: 🟢 Easy
Topics: Database

Solution

Problem Understanding

The problem asks us to transform data in a SQL table named Person. Each row represents a person, with three columns: person_id, name, and profession. The profession column is an ENUM containing one of six possible values: 'Doctor', 'Singer', 'Actor', 'Player', 'Engineer', or 'Lawyer'.

The task is to return a table where each person's name is concatenated with the first letter of their profession, enclosed in parentheses, e.g., 'Tyson(E)' for an engineer named Tyson. The output must be ordered by person_id in descending order, meaning the person with the largest person_id comes first.

Constraints ensure that each person_id is unique and that every row contains valid data. The problem guarantees that there are no missing or malformed entries. Edge cases to consider include single-row tables, professions with the same first letter, or names with special characters.

Approaches

A brute-force approach would be to manually iterate through each row, extract the first character of profession, concatenate it to the name string, and sort by person_id in descending order. This is correct but verbose in SQL if done without built-in functions.

The optimal approach leverages SQL string functions: CONCAT to join strings, LEFT or SUBSTRING to extract the first character of profession, and ORDER BY person_id DESC for sorting. This approach is concise, efficient, and fully leverages SQL's built-in capabilities, avoiding unnecessary row-by-row processing.

Approach Time Complexity Space Complexity Notes
Brute Force O(n log n) O(n) Iterate manually, extract first letters, concatenate, then sort
Optimal O(n log n) O(n) Use SQL string functions to concatenate and sort directly in the query

Algorithm Walkthrough

  1. Select Columns: Start with a SELECT statement to return person_id and the concatenated name.
  2. Extract First Letter: Use LEFT(profession, 1) to get the first character of the profession column.
  3. Concatenate Strings: Use CONCAT(name, '(', LEFT(profession, 1), ')') to form the required string format.
  4. Order Results: Use ORDER BY person_id DESC to ensure the largest person_id appears first.
  5. Return Table: Ensure the output column names match the expected result, e.g., name for the concatenated string.

Why it works: The CONCAT function accurately joins strings in SQL. Extracting the first letter using LEFT guarantees only the initial character of profession is included. Sorting by person_id ensures correct ordering. SQL handles all rows in a set-based manner efficiently.

Python Solution

For completeness, if this were to be done in Python after fetching the data, you could implement it as:

from typing import List, Tuple

def concatenate_name_profession(data: List[Tuple[int, str, str]]) -> List[Tuple[int, str]]:
    """
    data: List of tuples (person_id, name, profession)
    Returns: List of tuples (person_id, formatted_name)
    """
    result = [(pid, f"{name}({profession[0]})") for pid, name, profession in data]
    result.sort(key=lambda x: x[0], reverse=True)
    return result

This code takes a list of rows, extracts the first character of profession, concatenates it to the name, and sorts the result by person_id descending.

Go Solution

In Go, the solution would be similar in principle if reading from a database or slice:

package main

import (
	"fmt"
	"sort"
)

type Person struct {
	ID         int
	Name       string
	Profession string
}

func concatenateNameProfession(people []Person) []Person {
	result := make([]Person, len(people))
	for i, p := range people {
		result[i] = Person{
			ID:   p.ID,
			Name: fmt.Sprintf("%s(%c)", p.Name, p.Profession[0]),
		}
	}
	sort.Slice(result, func(i, j int) bool {
		return result[i].ID > result[j].ID
	})
	return result
}

In Go, slices are used to hold the data, fmt.Sprintf formats the string, and sort.Slice handles descending ordering. Indexing into the string with [0] gets the first character safely because the ENUM is guaranteed to be valid ASCII.

Worked Examples

Example input:

Person table:
+-----------+-------+------------+
| person_id | name  | profession |
+-----------+-------+------------+
| 1         | Alex  | Singer     |
| 3         | Alice | Actor      |
| 2         | Bob   | Player     |
| 4         | Messi | Doctor     |
| 6         | Tyson | Engineer   |
| 5         | Meir  | Lawyer     |
+-----------+-------+------------+

Step by step processing:

person_id name profession first letter concatenated
1 Alex Singer S Alex(S)
2 Bob Player P Bob(P)
3 Alice Actor A Alice(A)
4 Messi Doctor D Messi(D)
5 Meir Lawyer L Meir(L)
6 Tyson Engineer E Tyson(E)

After sorting by person_id descending:

6 Tyson(E)
5 Meir(L)
4 Messi(D)
3 Alice(A)
2 Bob(P)
1 Alex(S)

Complexity Analysis

Measure Complexity Explanation
Time O(n log n) Sorting by person_id dominates time complexity
Space O(n) New list or result table of size n is created

The SQL version relies on the database engine's internal sorting, which typically uses an efficient algorithm with O(n log n) complexity. Space is proportional to the number of rows returned.

Test Cases

# Provided example
assert concatenate_name_profession([
    (1, "Alex", "Singer"),
    (3, "Alice", "Actor"),
    (2, "Bob", "Player"),
    (4, "Messi", "Doctor"),
    (6, "Tyson", "Engineer"),
    (5, "Meir", "Lawyer")
]) == [
    (6, "Tyson(E)"),
    (5, "Meir(L)"),
    (4, "Messi(D)"),
    (3, "Alice(A)"),
    (2, "Bob(P)"),
    (1, "Alex(S)")
]

# Single row
assert concatenate_name_profession([(1, "John", "Doctor")]) == [(1, "John(D)")]

# All professions have the same first letter
assert concatenate_name_profession([
    (1, "A", "Actor"),
    (2, "B", "Actor")
]) == [(2, "B(A)"), (1, "A(A)")]

# Names with special characters
assert concatenate_name_profession([
    (1, "O'Neil", "Engineer"),
    (2, "Anne-Marie", "Lawyer")
]) == [(2, "Anne-Marie(L)"), (1, "O'Neil(E)")]
Test Why
Provided example Validates typical multi-row case
Single row Ensures function handles minimal input
Same first letter Checks correct extraction even when letters repeat
Special characters Validates that names with punctuation are handled

Edge Cases

Single-row table: If the table has only one person, the algorithm should still extract the first letter and return correctly. Both Python and Go handle this naturally due to iteration over the list or slice.

Names with non-alphabetic characters: Names containing spaces, hyphens, or apostrophes should still concatenate correctly. Using CONCAT in SQL or f-string/fmt.Sprintf ensures no errors occur.

Professions starting with the same letter: Multiple rows can have professions like 'Doctor' and 'Designer'. Extracting the first character is independent of duplicates, so no incorrect overwriting occurs. Sorting by person_id ensures order is preserved.

Empty or null names/professions: The problem guarantees valid input, but defensive code could check for NULL in a real-world scenario. In this problem, no additional handling is needed.