Las preguntas SQL son uno de los temas clave de la entrevista para un analista de datos o productos, así como para un analista de negocios. Las principales empresas tecnológicas, incluidas las extranjeras como Amazon, Uber y Facebook, son particularmente minuciosas a la hora de evaluar los conocimientos de un candidato en esta área.
Al prepararse para una entrevista de este tipo, puede ser difícil determinar en detalle todas las opciones posibles para las preguntas SQL. Para ayudarte a lidiar con esto, he preparado una breve guía basada en mi propia experiencia: he sido candidato a un puesto y empleador.
Las tareas SQL se pueden dividir en 4 niveles. Como parte de la guía, veremos cada uno de ellos junto con ejemplos estándar para la práctica. Si quieres aprender mejor el material, no vayas directamente a la solución.
Nivel 1. Tareas basadas en funciones agregadas
SQL es ideal para la agregación. Hay muchas funciones para esto, tales como, etc. El conocimiento de tales funciones es el nivel básico de conocimiento que se espera de un candidato.SUM()AVG()MAX()MIN()COUNT()
Considere la siguiente tabla de empleados. Cada línea contiene información sobre el empleado, como el departamento, el salario, el gerente, etc.
-- Table: employees
-- | dept_id | employee_id | amount | manager_id |
-- |---------|-------------|--------|------------|
-- | 1 | 1 | 8000 | 3 |
-- | 1 | 2 | 5000 | 3 |
-- | 1 | 3 | 10000 | null |
-- | 2 | 4 | 15000 | null |
-- | 2 | 5 | 16000 | 4 |
-- | 3 | 6 | 8000 | null |En función de esta tabla, escriba una consulta SQL para encontrar los identificadores de los empleados que más ganan en cada departamento.
La mejor manera de resolver cualquier problema es presentarlo en forma de lógica paso a paso. En este caso, determinamos la cantidad más alta para cada departamento. A continuación, definimos el formato de salida, para el cual solo se necesita el identificador employee_id.
-- Parte 1: Obtener el salario más alto en cada departamento
SELECT MAX(amount) AS salario
FROM employees
GROUP BY dept_id
-- Parte 2: Obtener el formato de salida deseado para employee_id
-- Dado que employee_id no se puede utilizar directamente en el grupo mediante agregación, podemos recurrir a subconsultas.
SELECT e1.employee_id
FROM employees e1
WHERE e1.amount IN (
SELECT MAX(e2.amount) AS amount
FROM employees as e2
GROUP BY e2.dept_id
HAVING e1.dept_id = e2.dept_id )
Nivel 2. Tareas basadas en JOIN y SETS
SQL proporciona la capacidad de combinar los resultados de dos o más tablas mediante JOIN. Los JOIN más populares incluyen INNER JOIN, LEFT JOIN, RIGHT JOIN y CROSS JOIN. Los operadores SET más conocidos son UNION, UNION ALL, EXCEPT e INTERSECT.
Volvamos a la tabla de empleados anterior. Escriba una consulta SQL para identificar a los empleados que ganan más que su jefe.
-- Parte 1: Emparejar el salario del gerente y el salario del empleado mediante una autoasociación
SELECT e1.employee_id
FROM employees as e1
LEFT JOIN employees as e2 ON e1.manager_id = e2.employee_id
-- Parte 2: Filtrar los empleados que ganan más que su gerente
SELECT e1.employee_id
FROM employees as e1
LEFT JOIN employees as e2 ON e1.manager_id = e2.employee_id
AND e1.amount > e2.amount
Nivel 3. Tareas basadas en una característica de Windows
Las funciones de ventana, también conocidas como funciones analíticas, son una característica destacada del lenguaje SQL. Algunas de las funciones analíticas más populares incluyen RANK(), DENSE_RANK(), LEAD(), LAG(), entre otras.
Volvamos a la primera tarea. Utilizamos una subconsulta para identificar al empleado mejor pagado. Puede hacer lo mismo con la función de Windows. Intenta resolver este problema sin mirar la solución.
-- Parte 1: Clasificar los identificadores de empleados por el salario más alto para cada departamento utilizando la función DENSE_RANK()
SELECT employee_id,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY amount DESC) rnk
FROM employees
-- Parte 2: Filtrar las filas donde rnk = 1
SELECT employee_id
FROM
(SELECT employee_id,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY amount DESC) rnk
FROM employees) a
WHERE rnk = 1
Nivel 4. Tareas basadas en una combinación de los niveles mencionados anteriormente
A primera vista, algunas tareas pueden parecer desalentadoras. Un enfoque lógico paso a paso se reconoce como la mejor estrategia para resolverlos. Divide la tarea en partes más pequeñas.
La práctica es el camino hacia la perfección. Cuanto más resuelva estos problemas, más fácil será desglosarlos lógicamente y encontrar una solución.
Considere la siguiente tabla de asistencia. Cada línea contiene la identificación del empleado y la fecha de la visita a la oficina.
Escriba una consulta SQL para encontrar la serie más larga de eventos para cada empleado. La salida debe contener el nombre del empleado y la serie más larga de eventos.
-- Table: attendance
-- | employee_id | attend_dt |
-- |-------------|-------------|
-- | 1 | 2022-01-01 |
-- | 1 | 2022-01-02 |
-- | 1 | 2022-01-05 |
-- | 2 | 2022-01-01 |
-- | 2 | 2022-01-02 |
-- | 2 | 2022-01-04 |
--`| 2 | 2022-01-05 |
-- | 2 | 2022-01-06 |
-- | 3 | 2022-01-02 |
-- | 3 | 2022-01-04 |
-- Table: employees
-- | employee_id | name |
-- |-------------|-------------|
-- | 1 | samuel |
-- | 2 | karthik |
-- | 3 | casey |-- Parte 1: Asignar un identificador a cada fila en la tabla
SELECT *, ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY attend_dt ASC) AS rn
FROM attendance
-- Parte 2: Encontrar el indicador "day" del campo de fecha y la diferencia entre rn y day
-- Esto nos ayudará a crear grupos de series continuas
SELECT *, DAY(attend_at) - rn AS day
FROM
(SELECT *, ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY attend_at ASC) AS rn
FROM attendance)
-- Ahora nuestra tabla se verá así
-- rn| employee_id | attend_dt | day | group_name(day-rn)|
-- --|-------------|-------------|-----|--------------------
-- 1 | 1 | 2022-01-01 | 1 | 0
-- 2 | 1 | 2022-01-02 | 2 | 0
-- 3 | 1 | 2022-01-05 | 5 | 2
-- 1 | 2 | 2022-01-01 | 1 | 0
-- 2 | 2 | 2022-01-02 | 2 | 0
-- 3 | 2 | 2022-01-04 | 4 | 1
-- 4 | 2 | 2022-01-05 | 5 | 1
-- 5 | 2 | 2022-01-06 | 6 | 1
-- 1 | 3 | 2022-01-02 | 2 | 1
-- 2 | 3 | 2022-01-04 | 4 | 2
-- Parte 3: Encontrar la cuenta para cada indicador de group_name y cada empleado
SELECT employee_id, group_name, COUNT(*) AS streak
FROM
(SELECT *, (DAY(attend_at) - rn) AS group_name
FROM
(SELECT *, ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY attend_at ASC) AS rn
FROM attendance) a) b
GROUP BY employee_id, group_name
-- Parte 4: Encontrar la serie de eventos más larga
SELECT employee_id, MAX(streak) AS longest_streak
FROM
(SELECT employee_id, group_name, COUNT(*) AS streak
FROM
(SELECT *, (DAY(attend_at) - rn) AS group_name
FROM
(SELECT *, ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY attend_at ASC) AS rn
FROM attendance) a) b
GROUP BY employee_id, group_name) c
-- Parte 5: Ordenar los datos en el formato de salida deseado
SELECT e.name, d.longest_streak
FROM
(SELECT employee_id, MAX(streak) AS longest_streak
FROM
(SELECT employee_id, group_name, COUNT(*) AS streak
FROM
(SELECT *, (DAY(attend_at) - rn) AS group_name
FROM
(SELECT *, ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY attend_at ASC) AS rn
FROM attendance) a) b
GROUP BY employee_id, group_name) c) d
JOIN
(SELECT * FROM employees) e ON d.employee_id = e.employee_id
