Problem

Table: Person

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

+-------------+---------+
| 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.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

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.

Solution

Method 1 – SQL String Concatenation

Intuition

We can use SQL string functions to concatenate the name with the first letter of the profession, enclosed in parentheses. This can be done using CONCAT and LEFT (or SUBSTRING) functions.

Approach

  1. Select person_id and concatenate name with ‘(’ + first letter of profession + ‘)’.
  2. Order the result by person_id in descending order.

Code

1
2
3

SELECT person_id, CONCAT(name, '(', LEFT(profession, 1), ')') AS name
FROM Person
ORDER BY person_id DESC;
1
2
3

SELECT person_id, name || '(' || LEFT(profession, 1) || ')' AS name
FROM Person
ORDER BY person_id DESC;
1
2
3
4
5
6

import pandas as pd

def concatenate_name_profession(person: pd.DataFrame) -> pd.DataFrame:
    df = person.copy()
    df['name'] = df['name'] + '(' + df['profession'].str[0] + ')'
    return df[['person_id', 'name']].sort_values('person_id', ascending=False)

Complexity

  • ⏰ Time complexity: O(n), where n is the number of rows in the table.
  • 🧺 Space complexity: O(n), for the output table.