Table: Person
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| person_id | int |
| name | varchar |
| profession | ENUM |
+-------------+---------+
person_id is the primary key (column with a unique value) for this table.
Each row in this table contains a person's ID, name, and profession.
The profession column in an enum of the type ('Doctor', 'Singer', 'Actor', 'Player', 'Engineer', or 'Lawyer')
Write a solution to report each person's name followed by the first letter of their profession enclosed in parentheses.
Return the result table ordered by person_id in descending order.
The result format is shown in the following example.
Example 1:
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 | +-----------+-------+------------+ Output: +-----------+----------+ | person_id | name | +-----------+----------+ | 6 | Tyson(E) | | 5 | Meir(L) | | 4 | Messi(D) | | 3 | Alice(A) | | 2 | Bob(P) | | 1 | Alex(S) | +-----------+----------+ Explanation: Note that there should not be any white space between the name and the first letter of the profession.
Problem Overview: The task asks you to return each person's name concatenated with their profession in parentheses. The result should combine two columns from the same row into a formatted string such as "Alice(Doctor)". This is a straightforward SQL string manipulation problem.
Approach 1: SQL CONCAT Function (O(n) time, O(1) space)
Scan the table and build the formatted output directly using the SQL CONCAT() function. For each row, concatenate the name, an opening parenthesis '(', the profession, and a closing parenthesis ')'. The database engine processes each row once, producing the formatted string in the result set. Since no additional data structures or joins are required, the space overhead remains constant.
This approach relies purely on SQL string operations, which are optimized inside the database engine. The query effectively performs a linear pass over the table, making the time complexity O(n), where n is the number of rows. The result is generated on the fly without storing intermediate structures.
Problems like this test your familiarity with basic SQL functions rather than algorithmic complexity. Understanding functions such as CONCAT, CONCAT_WS, and other string operators is essential when working with database queries that format or transform output fields.
You could also achieve the same result using alternative SQL syntax such as the concatenation operator (depending on the database) or nested string functions. However, CONCAT() is the clearest and most portable option in SQL environments like MySQL.
Recommended for interviews: The direct CONCAT() solution is exactly what interviewers expect. It demonstrates that you know how to manipulate column values using built-in SQL functions. There is no meaningful brute-force alternative here since the operation naturally maps to a single table scan. Focus on writing a clean query that formats the output correctly using standard database string functions.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| SQL CONCAT Function | O(n) | O(1) | Best general solution for formatting two columns into a single string |
| CONCAT_WS or String Operator | O(n) | O(1) | Useful when working with databases that support different concatenation syntax |
Leetcode 2504 - Concatenate Name & Profession CONCAT() LEFT() - Solved by Everyday Data Science • Everyday Data Science • 595 views views
Practice Concatenate the Name and the Profession with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor