SQL · OCCUPATIONS: formato y pivote

Esta serie mantiene la mente fresca resolviendo, uno a uno, los ejercicios de HackerRank. Cada entrada toma un tema concreto y lo agota; todas las consultas están en MySQL salvo donde se indique.

Te invito a intentar cada ejercicio antes de leer la solución.

Dos ejercicios sobre una tabla de dos columnas que terminan siendo de formato: uno arma cadenas con CONCAT y LEFT, y el otro pivota la tabla entera sin que MySQL tenga un PIVOT.

Occupation

The OCCUPATIONS table is described as follows:

The PADS

Generate the following two result sets:

1. Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses). For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).
2. Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:

There are a total of [occupation_count] [occupation]s.

where [occupation_count] is the number of occurrences of an occupation in OCCUPATIONS and [occupation] is the lowercase occupation name. If more than one Occupation has the same [occupation_count], they should be ordered alphabetically.

Note: There will be at least two entries in the table for each type of occupation.

Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.

Sample Input

An OCCUPATIONS table that contains the following records:

Sample Output

Ashley(P)
Christeen(P)
Jane(A)
Jenny(D)
Julia(A)
Ketty(P)
Maria(A)
Meera(S)
Priya(S)
Samantha(D)
There are a total of 2 doctors.
There are a total of 2 singers.
There are a total of 3 actors.
There are a total of 3 professors.

Explanation

The results of the first query are formatted to the problem description's specifications. The results of the second query are ascendingly ordered first by number of names corresponding to each profession (2 ≤ 2 ≤ 3 ≤ 3), and then alphabetically by profession (doctor ≤ singer, and actor ≤ professor).

Solución

Para este ejercicio necesitamos combinar varias funciones vistas anteriormente. Una de ellas es LEFT, que nos permite obtener la primera letra de la palabra en la columna occupation. También usamos CONCAT para dar el formato que se nos pide en ambas consultas. Por último, LOWER se utiliza para convertir el nombre de la ocupación a minúsculas, asegurando así que el test se pase correctamente.

SELECT
CONCAT(name, "(", LEFT(occupation,1), ")")
FROM occupations
ORDER BY name;

SELECT
CONCAT('There are a total of ', COUNT(occupation), ' ', LOWER(occupation), 's.')
FROM occupations
GROUP BY occupation
ORDER BY COUNT(occupation), occupation;

Occupations

Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output should consist of four columns (Doctor, Professor, Singer, and Actor) in that specific order, with their respective names listed alphabetically under each column.

Note: Print NULL when there are no more names corresponding to an occupation.

Input Format

The OCCUPATIONS table is described as follows:

Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.

Sample Input

An OCCUPATIONS table that contains the following records:

Sample Output

Jenny    Ashley    Meera    Jane
Samantha Christeen Priya    Julia
NULL     Ketty     NULL     Maria

Explanation

The first column is an alphabetically ordered list of Doctor names. The second column is an alphabetically ordered list of Professor names. The third column is an alphabetically ordered list of Singer names. The fourth column is an alphabetically ordered list of Actor names. The empty cell data for columns with less than the maximum number of names per occupation (in this case, the Professor and Actor columns) are filled with NULL values.

Solución

Para resolver este ejercicio necesitamos pivotar la tabla, convirtiendo los valores de la columna Occupation en encabezados de columnas. Como MySQL no tiene una función nativa PIVOT, simulamos este comportamiento usando una subconsulta con ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) AS row_num. Esto asigna un número consecutivo a cada nombre dentro de su profesión (el primer Doctor será 1, el segundo 2, etc.), lo que nos permite alinear las filas de diferentes ocupaciones bajo un mismo índice. Luego, en la consulta principal agrupamos por row_num y usamos CASE WHEN para evaluar cada ocupación: si coincide, devolvemos el nombre; si no, devolvemos NULL.

La razón por la que usamos MAX (o MIN) como función de agregación es un truco para colapsar los grupos. Al agrupar por row_num, cada columna generada por el CASE WHEN tendrá un solo valor no nulo y varios NULL (uno por cada fila que no pertenece a esa ocupación). Como MAX funciona con texto e ignora los valores NULL, extrae ese único nombre válido. Si una ocupación no tiene suficientes registros para ese row_num, todos los valores serán NULL y MAX devolverá NULL, cumpliendo así con la regla de imprimir NULL cuando ya no hay más nombres para esa ocupación.

SELECT
    MAX(CASE WHEN occupation = 'Doctor' THEN name END) AS Doctor,
    MAX(CASE WHEN occupation = 'Professor' THEN name END) AS Professor,
    MAX(CASE WHEN occupation = 'Singer' THEN name END) AS Singer,
    MAX(CASE WHEN occupation = 'Actor' THEN name END) AS Actor
FROM (
    SELECT 
        name, 
        occupation,
        ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) AS row_num
    FROM occupations
) AS temp
GROUP BY row_num
ORDER BY row_num;