SQL · Subconsultas y HAVING

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.

Cuatro ejercicios donde la condición no cabe en un WHERE: hay que contar primero y filtrar después, sumar sólo el máximo de cada grupo con una ventana, y al final comparar cada fila contra el mínimo de su propio grupo.

Hackers

Top Competitors

Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! Write a query to print the respective hacker_id and name of hackers who achieved full scores for more than one challenge. Order your output in descending order by the total number of challenges in which the hacker earned a full score. If more than one hacker received full scores in same number of challenges, then sort them by ascending hacker_id

Input Format

The following tables contain contest data:

Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.

Difficulty: The difficulty_level is the level of difficulty of the challenge, and score is the maximum score that can be achieved for a challenge at that difficulty level.

Challenges: The challenge_id is the id of the challenge, the hacker_id is the id of the hacker who created the challenge, and difficulty_level is the level of difficulty of the challenge.

Submissions: The submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, challenge_id is the id of the challenge that the submission belongs to, and score is the score of the submission.

Sample Input

Hackers Table:

Difficulty Table:

Challenges Table:

Submissions Table:

Sample Output

90411 Joe

Explanation

Hacker 86870 got a score of 30 for challenge 71055 with a difficulty level of 2, so 86870 earned a full score for this challenge.

Hacker 90411 got a score of 30 for challenge 71055 with a difficulty level of 2, so 90411 earned a full score for this challenge.

Hacker 90411 got a score of 100 for challenge 66730 with a difficulty level of 6, so 90411 earned a full score for this challenge.

Only hacker 90411 managed to earn a full score for more than one challenge, so we print their hacker_id and name as space-separated values.

Solución

La idea principal es obtener un recuento de los hackers que han alcanzado el puntaje máximo en cada desafío. Para ello, debemos relacionar las tablas de modo que podamos comparar el puntaje máximo del desafío con el puntaje obtenido por el hacker, y luego filtrar por aquellos que tengan más de un desafío con puntaje máximo. Para esto realizamos un INNER JOIN con una subconsulta que, agrupando por hacker (GROUP BY), cuente cuántas veces coinciden exactamente ambos puntajes, sumando 1 por cada coincidencia para cada hacker. Así agrupamos los conteos.

Después, lo más fácil es filtrar solo a los que tengan más de un desafío completado con el puntaje máximo, ordenados de forma descendente.

SELECT
  hs.hacker_id,
  hs.name
FROM
  hackers AS hs
  INNER JOIN (
    SELECT
      sub.hacker_id,
      COUNT(
        CASE
          WHEN diff.score = sub.score THEN 1
        END
      ) AS matchs_score
    FROM
      difficulty AS diff
      INNER JOIN challenges AS chall ON chall.difficulty_level = diff.difficulty_level
      INNER JOIN submissions AS sub ON chall.challenge_id = sub.challenge_id
    GROUP BY
      sub.hacker_id
  ) AS hs_s ON hs.hacker_id = hs_s.hacker_id
WHERE hs_s.matchs_score >= 2
ORDER BY
  hs_s.matchs_score DESC;

Challenges

Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student.

Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

Input Format

The following tables contain challenge data:

• Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.

• Challenges: The challenge_id is the id of the challenge, and hacker_id is the id of the student who created the challenge.

Sample Input 0

Hackers Table

Challenges Table

Sample Output 0

21283 Angela 6
88255 Patrick 5
96196 Lisa 1

Sample Input 1

Hackers Table

Challenges Table

Sample Output 1

12299 Rose 6
34856 Angela 6
79345 Frank 4
80491 Patrick 3
81041 Lisa 1

Explanation

For Sample Case 0, we can get the following details:

Students 5077 and 62743 both created 4 challenges, but the maximum number of challenges created is 6, so these students are excluded from the result.

For Sample Case 1, we can get the following details:

Students 12299 and 34856 both created 6 challenges. Because 6 is the maximum number of challenges created, these students are included in the result.

Solución

Creo que este ha sido, por ahora, el ejercicio que más me ha confundido.

Tenemos que cumplir dos condiciones. La primera permite mostrar a todos los hackers que tengan el máximo número de challenges creados, incluso si varios hackers tienen exactamente la misma cantidad.

Para obtener el número de challenges de cada hacker usamos COUNT(*) y agrupamos mediante GROUP BY por hs.hacker_id y hs.name. Como cada grupo representa a un hacker, no necesitamos utilizar DISTINCT.

Volviendo a HAVING, necesitamos comprobar las dos condiciones.

Primera condición

Primero buscamos cuál es el máximo número de challenges creados por un hacker.

Para hacerlo, contamos los challenges de cada hacker_id y después obtenemos el máximo de esos conteos:

count_challs = (
    SELECT 
      MAX(challenge_count) 
    FROM 
      (
        SELECT 
          COUNT(*) AS challenge_count 
        FROM 
          challenges 
        GROUP BY 
          hacker_id
      ) AS counts
  )

La subconsulta interna obtiene algo similar a:

hacker_id | challenge_count
----------|----------------
1         | 4
2         | 7
3         | 7
4         | 3

Luego, MAX(challenge_count) obtiene 7.

Finalmente, comparamos ese 7 con el count_challs de cada hacker. De esta manera, todos los hackers que tengan el máximo número de challenges pueden pasar la condición, incluso si hay varios con ese mismo máximo.

Segunda condición

La segunda condición busca los números de challenges que aparecen una sola vez entre todos los hackers.

Para ello hacemos algo similar a la primera condición: primero contamos los challenges de cada hacker. Después agrupamos esos resultados por challenge_count para saber cuántas veces aparece cada cantidad.

OR count_challs IN (
    SELECT 
      challenge_count 
    FROM 
      (
        SELECT 
          COUNT(*) AS challenge_count 
        FROM 
          challenges 
        GROUP BY 
          hacker_id
      ) AS counts 
    GROUP BY 
      challenge_count 
    HAVING 
      COUNT(*) = 1
  )

Aquí IN es necesario porque la subconsulta puede devolver varios valores.

Por ejemplo, si tenemos:

challenge_count
---------------
3
4
4
7
7
7
10

Al hacer:

GROUP BY challenge_count

obtenemos los diferentes valores y podemos contar cuántas veces aparece cada uno.

Después:

HAVING COUNT(*) = 1

filtra los grupos cuya cantidad aparece exactamente una vez.

En este ejemplo, la subconsulta devolvería:

3
10

Por lo tanto, la condición:

count_challs IN (...)

permite que también pasen los hackers cuyo número de challenges sea un valor que aparece solamente una vez.

SELECT 
  hs.hacker_id, 
  hs.name, 
  COUNT(ch.challenge_id) as count_challs 
FROM 
  hackers as hs 
  LEFT JOIN challenges ch ON ch.hacker_id = hs.hacker_id 
GROUP BY 
  hs.hacker_id, 
  hs.name 
HAVING 
  count_challs = (
    SELECT 
      MAX(challenge_count) 
    FROM 
      (
        SELECT 
          COUNT(*) AS challenge_count 
        FROM 
          challenges 
        GROUP BY 
          hacker_id
      ) AS counts
  ) 
  OR count_challs IN (
    SELECT 
      challenge_count 
    FROM 
      (
        SELECT 
          COUNT(*) AS challenge_count 
        FROM 
          challenges 
        GROUP BY 
          hacker_id
      ) AS counts 
    GROUP BY 
      challenge_count 
    HAVING 
      COUNT(*) = 1
  ) 
ORDER BY 
  count_challs DESC, 
  hs.hacker_id ASC;

Contest Leaderboard

Julia acaba de terminar otro concurso y vuelve a necesitar ayuda con la tabla de posiciones.

La puntuación total de un hacker es la suma de sus puntuaciones máximas en cada challenge. Escribe una consulta que imprima el hacker_id, el name y la puntuación total, ordenados por puntuación descendente. Si varios hackers empatan, ordénalos por hacker_id ascendente. Excluye del resultado a los hackers con puntuación total 0.

Input Format

Hackers: el hacker_id identifica al hacker y name es su nombre.

Submissions: submission_id identifica el envío, hacker_id es quien lo hizo, challenge_id el challenge al que pertenece y score la puntuación de ese envío.

Sample Input

Hackers Table

Submissions Table

Sample Output

4071 Rose 191
74842 Lisa 174
84072 Bonnie 100
4806 Angela 89
26071 Frank 85
80305 Kimberly 67
49438 Patrick 43

Explanation

El hacker 4071 envió soluciones para los challenges 19797 y 49593, así que su total es 95 + max(43, 96) = 191.

El hacker 74842 envió soluciones para 19797 y 63132, así que su total es max(98, 5) + 76 = 174.

El hacker 84072 envió soluciones para 49593 y 63132, así que su total es 100 + 0 = 100.

Los totales de 4806, 26071, 80305 y 49438 se calculan igual.

Solución

Lo que queremos abordar es cómo sumar únicamente los puntajes máximos obtenidos en los challenges, sin importar cuántos intentos hubo, sino cuál de todos esos intentos es el máximo y sumarlo con los demás puntajes máximos obtenidos, descartando claramente los intentos anteriores en cada uno de ellos.

Para esto usamos PARTITION BY para dividir las sumas totales del score por cada hacker_id y así evitamos usar GROUP BY y WHERE, simplificando mucho más las cosas. Realizamos un INNER JOIN con una subconsulta meramente para filtrar solamente esos puntajes máximos para cada challenge, los cuales luego vamos a usar sumándolos en el SELECT.

SELECT 
  DISTINCT hs.hacker_id, 
  hs.name, 
  SUM(sub.score) OVER(PARTITION BY hs.hacker_id) as score_total 
FROM 
  hackers hs 
  JOIN (
    SELECT 
      sub_v2.hacker_id, 
      MAX(sub_v2.score) as score 
    FROM 
      submissions sub_v2 
    GROUP BY 
      sub_v2.hacker_id, 
      sub_v2.challenge_id
  ) sub ON sub.hacker_id = hs.hacker_id 
WHERE 
  sub.score > 0 
ORDER BY 
  score_total DESC, 
  hacker_id;

Harry Potter

Ollivander's Inventory

Problem Statement

Harry Potter and his friends are at Ollivander's with Ron, finally replacing Charlie's old broken wand.

Hermione decides the best way to choose is by determining the minimum number of gold galleons needed to buy each non-evil wand of high power and age. Write a query to print the id, age, coins_needed, and power of the wands that Ron's interested in, sorted in order of descending power. If more than one wand has same power, sort the result in order of descending age.

Input Format

The following tables contain data on the wands in Ollivander's inventory:

Wands: The id is the id of the wand, code is the code of the wand, coins_needed is the total number of gold galleons needed to buy the wand, and power denotes the quality of the wand (the higher the power, the better the wand is).

Wands_Property: The code is the code of the wand, age is the age of the wand, and is_evil denotes whether the wand is good for the dark arts. If the value of is_evil is 0, it means that the wand is not evil. The mapping between code and age is one-one, meaning that if there are two pairs, (code1, age1) and (code2, age2), then code1 ≠ code2 and age1 ≠ age2.

Sample Input

Wands Table:

Wands_Property Table:

Sample Output

9 45 1647 10
12 17 9897 10
1 20 3688 8
15 40 6018 7
19 20 7651 6
11 40 7587 5
10 20 504 5
18 40 3312 3
20 17 5689 3

Explanation

The wands with is_evil = 0 are considered. The result contains the wands with the minimum coins_needed for each power and age combination, sorted by descending power and then descending age.

Solución

En este ejercicio puede resultar un poco confuso. La idea es entender cómo podemos filtrar, sabiendo que la comparación se realiza para cada registro o fila. Es decir, si queremos encontrar, entre las varitas que comparten el mismo power y la misma age, la que requiere la menor cantidad de galeones, debemos hacer que en el WHERE coins_needed coincida con el mínimo necesario entre esas varitas, para que el resultado sea una sola varita entre muchas. Para esto, hacemos una subconsulta en la que comparamos el registro actual con los que vamos a filtrar entre las repeticiones; deben coincidir age y power, y además la varita no debe ser malvada, usando la columna is_evil, que toma valores 0 o 1 según si es malvada o no.

SELECT
    w.id,
    wp.age,
    w.coins_needed,
    w.power
FROM wands AS w
INNER JOIN wands_property AS wp
    ON wp.code = w.code
WHERE wp.is_evil = 0
  AND w.coins_needed = (
      SELECT MIN(w2.coins_needed)
      FROM wands AS w2
      INNER JOIN wands_property AS wp2
          ON wp2.code = w2.code
      WHERE wp2.is_evil = 0
        AND w2.power = w.power
        AND wp2.age = wp.age
  )
ORDER BY
    w.power DESC,
    wp.age DESC;