El primer 70% del curso de SQL parece bastante sencillo. Las dificultades comienzan en el 30% restante.De 2015 a 2019, pasé por cuatro entrevistas para los puestos de analista de datos y especialista en análisis de datos en más de una docena de empresas. Después de otra entrevista fallida en 2017, cuando me confundí acerca de las preguntas complejas de SQL, comencé a compilar un libro de tareas con preguntas SQL de complejidad media y alta para prepararme mejor para las entrevistas. Esta guía ha sido útil en la última ronda de entrevistas en 2019. Durante el año pasado, compartí esta guía con un par de amigos, y gracias al tiempo libre adicional debido a una pandemia, la pulí y compilé este documento.Hay muchos excelentes tutoriales SQL para principiantes. Mis favoritos sonCursos interactivos SQL y Select Star SQL de Codecademy por Zi Chung Kao. Pero en realidad, el primer 70% del curso de SQL es bastante simple, y las verdaderas dificultades comienzan en el 30% restante, que no están cubiertas en las guías para principiantes. Entonces, en las entrevistas para analistas de datos y especialistas en análisis de datos en compañías de tecnología, a menudo se hacen preguntas sobre este 30%.Sorprendentemente, no encontré una fuente exhaustiva sobre estos temas de dificultad media, así que compilé esta guía.Es útil para entrevistas, pero al mismo tiempo aumentará su efectividad en sus trabajos actuales y futuros. Personalmente, creo que algunas de las plantillas SQL mencionadas también son útiles para los sistemas ETL que ejecutan herramientas de informes y funciones de análisis de datos para identificar tendencias.
Debe comprender que durante las entrevistas con analistas de datos y analistas de datos, hacen preguntas no solo sobre SQL. Otros temas comunes incluyen la discusión de proyectos pasados, pruebas A / B, desarrollo métrico y problemas analíticos abiertos. Hace unos tres años, Quora publicó consejos sobre entrevistas para un puesto de analista de productos en Facebook. Allí, este tema se discute con más detalle. Sin embargo, si mejorar su conocimiento de SQL lo ayudará en su entrevista, entonces esta guía bien vale la pena.En el futuro, puedo transferir el código de esta guía a un sitio como Select Star SQLpara facilitar la escritura de sentencias SQL y ver el resultado de la ejecución del código en tiempo real. Como opción, agregue preguntas como problemas a la plataforma para prepararse para las entrevistas de LeetCode . Mientras tanto, solo quería publicar este documento para que las personas puedan familiarizarse con esta información.Suposiciones hechas y cómo usar el manual
Suposiciones sobre el conocimiento del lenguaje SQL: se supone que tiene conocimiento práctico de SQL. Probablemente lo use a menudo en el trabajo, pero quiere perfeccionar sus habilidades en temas como las autoasociaciones y las funciones de ventana.Cómo usar este manual: dado que a menudo se usa un tablero o un bloc de notas virtual en la entrevista (sin compilar el código), recomiendo tomar un lápiz y papel y escribir soluciones para cada problema, y después de completarlo, compare sus notas con las respuestas. ¡O calcule sus respuestas con un amigo que actuará como entrevistador!- Los errores menores de sintaxis no importan mucho durante una entrevista con una pizarra o un bloc de notas. Pero pueden distraer al entrevistador, así que idealmente trate de reducir su número para concentrar toda la atención en la lógica.
- Las respuestas dadas no son necesariamente la única forma de resolver cada problema. ¡No dude en escribir comentarios con soluciones adicionales que puede agregar a esta guía!
Consejos para resolver tareas complejas en entrevistas SQL
Primero, consejos estándar para todas las entrevistas de programación ...- Escuche atentamente la descripción del problema, repita la esencia del problema al entrevistador.
- Formule un caso límite para demostrar que realmente comprende el problema (es decir, una línea que no se incluirá en la consulta SQL final que va a escribir)
- ( ) , — : ,
- SQL, , . , .
Algunos de los problemas enumerados aquí están adaptados de las antiguas entradas de blog de Periscope (en su mayoría escritas por Sean Cook alrededor de 2014, aunque su autoría parece haber sido eliminada de los materiales después de que SiSense se fusionó con Periscope ), así como de las discusiones sobre StackOverflow. Si es necesario, las fuentes se marcan al comienzo de cada pregunta.En Select Star SQL también hay una buena selección de acertijos , temas complementarios de este documento.Tenga en cuenta que estas preguntas no son copias literales de las preguntas de mis propias entrevistas, y no se utilizaron en las empresas en las que trabajé o trabajo.Tareas de autoasociación
No. 1. Porcentaje de cambio mes a mes
Contexto: a menudo es útil saber cómo cambia una métrica clave, por ejemplo, la audiencia mensual de usuarios activos, de mes a mes. Digamos que tenemos una tabla logins
de esta forma:El | user_id | fecha |
| --------- | ------------ |
El | 1 | 2018-07-01 |
El | 234 2018-07-02 |
El | 3 | 2018-07-02 |
El | 1 | 2018-07-02 |
El | ... | ... |
El | 234 2018-10-04 |
Objetivo : encontrar el cambio porcentual mensual en la audiencia mensual de usuarios activos (MAU).Solución:(Esta solución, como los otros bloques de código en este documento, contiene comentarios sobre elementos de sintaxis SQL que pueden diferir entre las diferentes variantes SQL y otras notas)WITH mau AS
(
SELECT
DATE_TRUNC('month', date) month_timestamp,
COUNT(DISTINCT user_id) mau
FROM
logins
GROUP BY
DATE_TRUNC('month', date)
)
SELECT
a.month_timestamp previous_month,
a.mau previous_mau,
b.month_timestamp current_month,
b.mau current_mau,
ROUND(100.0*(b.mau - a.mau)/a.mau,2) AS percent_change
FROM
mau a
JOIN
mau b ON a.month_timestamp = b.month_timestamp - interval '1 month'
No. 2. Estructura del árbol de marcado
Contexto: suponga que tiene una tabla tree
con dos columnas: la primera indica los nodos y la segunda los nodos principales.nodo principal
12
2 5
3 5
4 3
5 NULL
La tarea: escribir SQL de tal manera que designemos cada nodo como interno (raíz), raíz (raíz) o nodo final / hoja (hoja), de modo que para los valores anteriores obtenga lo siguiente:node label
1 Leaf
2 Inner
3 Inner
4 Leaf
5 Root
(Nota: aquí se puede encontrar más información sobre la terminología de la estructura de datos en forma de árbol . Sin embargo, ¡no es necesario resolver este problema!)Solución:Gracias: esta es una solución más generalizada propuesta por Fabian Hoffman el 2 de mayo de 2020. Gracias, FabianWITH join_table AS
(
SELECT
cur.node,
cur.parent,
COUNT(next.node) AS num_children
FROM
tree cur
LEFT JOIN
tree next ON (next.parent = cur.node)
GROUP BY
cur.node,
cur.parent
)
SELECT
node,
CASE
WHEN parent IS NULL THEN "Root"
WHEN num_children = 0 THEN "Leaf"
ELSE "Inner"
END AS label
FROM
join_table
Solución alternativa, sin conexiones explícitas:Reconocimiento: William Chardgin el 2 de mayo de 2020 llamó la atención sobre la necesidad de la condición de WHERE parent IS NOT NULL
que esta solución regrese Leaf
en su lugar NULL
. Gracias William!SELECT
node,
CASE
WHEN parent IS NULL THEN 'Root'
WHEN node NOT IN
(SELECT parent FROM tree WHERE parent IS NOT NULL) THEN 'Leaf'
WHEN node IN (SELECT parent FROM tree) AND parent IS NOT NULL THEN 'Inner'
END AS label
from
tree
No. 3. Retención de usuarios por mes (varias partes)
Reconocimiento: esta tarea fue adaptada del artículo del blog de SiSense, "Uso de autoasociaciones para calcular la retención, el flujo de salida y la reactivación " .Parte 1
Contexto: supongamos que tenemos estadísticas sobre la autorización del usuario en un sitio en la tabla logins
:El | user_id | fecha |
| --------- | ------------ |
El | 1 | 2018-07-01 |
El | 234 2018-07-02 |
El | 3 | 2018-07-02 |
El | 1 | 2018-07-02 |
El | ... | ... |
El | 234 2018-10-04 |
Tarea: escriba una solicitud que reciba el número de usuarios retenidos por mes. En nuestro caso, este parámetro se define como el número de usuarios que iniciaron sesión en el sistema en este y el mes anterior.Decisión:SELECT
DATE_TRUNC('month', a.date) month_timestamp,
COUNT(DISTINCT a.user_id) retained_users
FROM
logins a
JOIN
logins b ON a.user_id = b.user_id
AND DATE_TRUNC('month', a.date) = DATE_TRUNC('month', b.date) +
interval '1 month'
GROUP BY
date_trunc('month', a.date)
Reconocimiento:Tom Moertel señaló que la duplicación previa de user_id antes de la autounión hace que la solución sea más eficiente, y sugirió el siguiente código. Gracias tom!Solución alternativa:WITH DistinctMonthlyUsers AS (
SELECT DISTINCT
DATE_TRUNC('MONTH', a.date) AS month_timestamp,
user_id
FROM logins
)
SELECT
CurrentMonth.month_timestamp month_timestamp,
COUNT(PriorMonth.user_id) AS retained_user_count
FROM
DistinctMonthlyUsers AS CurrentMonth
LEFT JOIN
DistinctMonthlyUsers AS PriorMonth
ON
CurrentMonth.month_timestamp = PriorMonth.month_timestamp + INTERVAL '1 MONTH'
AND
CurrentMonth.user_id = PriorMonth.user_id
Parte 2
Tarea: ahora tomamos la tarea anterior de calcular el número de usuarios retenidos por mes, y lo volteamos. Escribiremos una solicitud para contar los usuarios que no han regresado al sitio este mes. Es decir, usuarios "perdidos".Decisión:SELECT
DATE_TRUNC('month', a.date) month_timestamp,
COUNT(DISTINCT b.user_id) churned_users
FROM
logins a
FULL OUTER JOIN
logins b ON a.user_id = b.user_id
AND DATE_TRUNC('month', a.date) = DATE_TRUNC('month', b.date) +
interval '1 month'
WHERE
a.user_id IS NULL
GROUP BY
DATE_TRUNC('month', a.date)
Tenga en cuenta que este problema también se puede resolver con LEFT
o RIGHT
.Parte 3
Nota: esta es probablemente una tarea más difícil de lo que se le ofrecerá en una entrevista real. Piénselo más como un rompecabezas, o puede saltarse y pasar a la siguiente tarea.Contexto : entonces hicimos un buen trabajo en dos problemas anteriores. Según los términos de la nueva tarea, ahora tenemos una tabla de usuarios perdidos user_churns
. Si el usuario estuvo activo en el último mes, pero luego no estuvo activo en este, se ingresa en la tabla de este mes. Así es como se ve user_churns
:El | user_id | mes_fecha |
| --------- | ------------ |
El | 1 | 2018-05-01 |
El | 234 2018-05-01 |
El | 3 | 2018-05-01 |
El | 12 | 2018-05-01 |
El | ... | ... |
El | 234 2018-10-01 |
Tarea : ahora desea realizar un análisis de cohorte, es decir, un análisis de la totalidad de usuarios activos que se han reactivado en el pasado . Crea una tabla con estos usuarios. Puede usar tablas user_churns
y crear una cohorte logins
. En Postgres, se puede acceder a la marca de tiempo actual a través de current_timestamp
.Decisión:WITH user_login_data AS
(
SELECT
DATE_TRUNC('month', a.date) month_timestamp,
a.user_id,
MAX(b.month_date) as most_recent_churn,
MAX(DATE_TRUNC('month', c.date)) as most_recent_active
FROM
logins a
JOIN
user_churns b
ON a.user_id = b.user_id AND DATE_TRUNC('month', a.date) > b.month_date
JOIN
logins c
ON a.user_id = c.user_id
AND
DATE_TRUNC('month', a.date) > DATE_TRUNC('month', c.date)
WHERE
DATE_TRUNC('month', a.date) = DATE_TRUNC('month', current_timestamp)
GROUP BY
DATE_TRUNC('month', a.date),
a.user_id
HAVING
most_recent_churn > most_recent_active
No. 4. Total creciente
Reconocimiento: Esta tarea fue adaptada del artículo del blog de SiSense , Cash Flow Modeling in SQL .Contexto: supongamos que tenemos una tabla transactions
de esta forma:El | fecha | flujo de caja |
| ------------ | ----------- |
El | 2018-01-01 | -1000 |
El | 2018-01-02 | -100 |
El | 2018-01-03 | 50
El | ... | ... |
¿Dónde cash_flow
están los ingresos menos los costos de cada día?Objetivo: escribir una solicitud para obtener un total acumulado de flujo de caja todos los días de tal manera que al final obtenga una tabla de esta forma:El | fecha | cumulative_cf |
| ------------ | --------------- |
El | 2018-01-01 | -1000 |
El | 2018-01-02 | -1100 |
El | 2018-01-03 | -1050 |
El | ... | ... |
Decisión:SELECT
a.date date,
SUM(b.cash_flow) as cumulative_cf
FROM
transactions a
JOIN b
transactions b ON a.date >= b.date
GROUP BY
a.date
ORDER BY
date ASC
Una solución alternativa que utiliza una función de ventana (¡más eficiente!):SELECT
date,
SUM(cash_flow) OVER (ORDER BY date ASC) as cumulative_cf
FROM
transactions
ORDER BY
date ASC
No. 5. Media móvil
Reconocimiento: esta tarea está adaptada del artículo del blog de SiSense , Medias Móviles en MySQL y SQL Server .Nota: el promedio móvil se puede calcular de varias maneras. Aquí usamos el promedio anterior. Por lo tanto, la métrica para el séptimo día del mes será el promedio de los seis días anteriores y él mismo.Contexto : supongamos que tenemos una tabla signups
de esta forma:El | fecha | inscripciones |
| ------------ | ---------- |
El | 2018-01-01 | 10 |
El | 2018-01-02 | 20 |
El | 2018-01-03 | 50
El | ... | ... |
El | 2018-10-01 | 35
Tarea : escriba una solicitud para obtener un promedio móvil de 7 días de registros diarios.Decisión:SELECT
a.date,
AVG(b.sign_ups) average_sign_ups
FROM
signups a
JOIN
signups b ON a.date <= b.date + interval '6 days' AND a.date >= b.date
GROUP BY
a.date
No. 6. Varias condiciones de conexión.
Reconocimiento: esta tarea está adaptada del artículo del blog de SiSense, "Analizando su correo electrónico usando SQL" .Contexto: supongamos que nuestra tabla emails
contiene correos electrónicos enviados desde la dirección zach@g.com
y recibidos en ella:El | id | sujeto | de | a | marca de tiempo |
| ---- | ---------- | -------------- | -------------- | --- ------------------ |
El | 1 | Yosemite | zach@g.com | thomas@g.com | 2018-01-02 12:45:03 |
El | 2 | Big Sur | sarah@g.com | thomas@g.com | 2018-01-02 16:30:01 |
El | 3 | Yosemite | thomas@g.com | zach@g.com | 2018-01-02 16:35:04 |
El | 4 | Corriendo | jill@g.com | zach@g.com | 2018-01-03 08:12:45 |
El | 5 | Yosemite | zach@g.com | thomas@g.com | 2018-01-03 14:02:01 |
El | 6 | Yosemite | thomas@g.com | zach@g.com | 2018-01-03 15:01:05 |
El | .. | .. | .. | .. | .. |
Tarea: escriba una solicitud para obtener el tiempo de respuesta para cada carta ( id
) enviada a zach@g.com
. No incluya cartas a otras direcciones. Supongamos que cada hilo tiene un tema único. Tenga en cuenta que el hilo puede tener varias cartas de ida y vuelta entre zach@g.com
y otros destinatarios.Decisión:SELECT
a.id,
MIN(b.timestamp) - a.timestamp as time_to_respond
FROM
emails a
JOIN
emails b
ON
b.subject = a.subject
AND
a.to = b.from
AND
a.from = b.to
AND
a.timestamp < b.timestamp
WHERE
a.to = 'zach@g.com'
GROUP BY
a.id
Tareas para funciones de ventana
No. 1. Encuentra el identificador con el valor máximo
Contexto: supongamos que tenemos una tabla salaries
con datos sobre departamentos y salarios de empleados en el siguiente formato: depname | empno | salario |
----------- + ------- + -------- +
desarrollar | 11 | 5200 |
desarrollar | 7 | 4200 |
desarrollar | 9 | 4500 |
desarrollar | 8 | 6000 |
desarrollar | 10 | 5200 |
personal | 5 | 3500 |
personal | 2 | 3900 |
ventas | 3 | 4800 |
ventas | 1 | 5000 |
ventas | 4 | 4800 |
Tarea : escriba una solicitud para obtener empno
el salario más alto. ¡Asegúrese de que su solución maneje casos de salarios iguales!Decisión:WITH max_salary AS (
SELECT
MAX(salary) max_salary
FROM
salaries
)
SELECT
s.empno
FROM
salaries s
JOIN
max_salary ms ON s.salary = ms.max_salary
Solución alternativa usando RANK()
:WITH sal_rank AS
(SELECT
empno,
RANK() OVER(ORDER BY salary DESC) rnk
FROM
salaries)
SELECT
empno
FROM
sal_rank
WHERE
rnk = 1;
No. 2. Valor promedio y clasificación con una función de ventana (varias partes)
Parte 1
Contexto : supongamos que tenemos una tabla salaries
en este formato: depname | empno | salario |
----------- + ------- + -------- +
desarrollar | 11 | 5200 |
desarrollar | 7 | 4200 |
desarrollar | 9 | 4500 |
desarrollar | 8 | 6000 |
desarrollar | 10 | 5200 |
personal | 5 | 3500 |
personal | 2 | 3900 |
ventas | 3 | 4800 |
ventas | 1 | 5000 |
ventas | 4 | 4800 |
Tarea: escriba una consulta que devuelva la misma tabla, pero con una nueva columna que muestre el salario promedio del departamento. Esperaríamos una tabla como esta: depname | empno | salario | avg_salary |
----------- + ------- + -------- + ------------ +
desarrollar | 11 | 5200 | 5020 |
desarrollar | 7 | 4200 | 5020 |
desarrollar | 9 | 4500 | 5020 |
desarrollar | 8 | 6000 | 5020 |
desarrollar | 10 | 5200 | 5020 |
personal | 5 | 3500 | 3700 |
personal | 2 | 3900 | 3700 |
ventas | 3 | 4800 | 4867 |
ventas | 1 | 5000 | 4867 |
ventas | 4 | 4800 | 4867 |
Decisión:SELECT
*,
ROUND(AVG(salary),0) OVER (PARTITION BY depname) avg_salary
FROM
salaries
Parte 2
Tarea: escriba una consulta que agregue una columna con la posición de cada empleado en la hoja de tiempo en función de su salario en su departamento, donde el empleado con el salario más alto obtiene la posición 1. Esperaríamos una tabla de esta forma: depname | empno | salario | Salario_rank |
----------- + ------- + -------- + ------------- +
desarrollar | 11 | 5200 | 2 |
desarrollar | 7 | 4200 | 5 |
desarrollar | 9 | 4500 | 4 |
desarrollar | 8 | 6000 | 1 |
desarrollar | 10 | 5200 | 2 |
personal | 5 | 3500 | 2 |
personal | 2 | 3900 | 1 |
ventas | 3 | 4800 | 2 |
ventas | 1 | 5000 | 1 |
ventas | 4 | 4800 | 2 |
Decisión:SELECT
*,
RANK() OVER(PARTITION BY depname ORDER BY salary DESC) salary_rank
FROM
salaries
Otras tareas de dificultad media y alta.
No. 1. Histogramas
Contexto: Digamos que tenemos una tabla sessions
donde cada fila representa una sesión de transmisión de video con una duración en segundos:El | session_id | longitud_segundos |
| ------------ | ---------------- |
El | 1 | 23 |
El | 2 | 453
El | 3 | 27
El | .. | .. |
Tarea: escriba una consulta para calcular el número de sesiones que caen en intervalos de cinco segundos, es decir, para el fragmento anterior, el resultado será algo como esto:El | cubo | contar |
| --------- | ------- |
El | 20-25 | 2 |
El | 450-455 | 1 |
El puntaje máximo cuenta para las etiquetas de línea adecuadas ("5-10", etc.)Solución:WITH bin_label AS
(SELECT
session_id,
FLOOR(length_seconds/5) as bin_label
FROM
sessions
)
SELECT
CONCATENTATE(STR(bin_label*5), '-', STR(bin_label*5+5)) bucket,
COUNT(DISTINCT session_id) count
GROUP BY
bin_label
ORDER BY
bin_label ASC
No. 2. Conexión cruzada (varias partes)
Parte 1
Contexto: supongamos que tenemos una tabla state_streams
donde el nombre del estado y el número total de horas de transmisión desde el alojamiento de video se indican en cada línea:El | estado | corrientes_total |
| ------- | --------------- |
El | NC | 34569 |
El | SC | 33999 |
El | CA | 98324 |
El | MA | 19345 |
El | .. | .. |
(De hecho, las tablas agregadas de este tipo generalmente tienen una columna de fecha, pero la excluiremos para esta tarea)Tarea: escriba una consulta para obtener pares de estados con un número total de hilos dentro de mil entre sí. Para el fragmento anterior, nos gustaría ver algo como:El | estado_a | estado_b |
| --------- | --------- |
El | NC | SC |
El | SC | NC |
Decisión:SELECT
a.state as state_a,
b.state as state_b
FROM
state_streams a
CROSS JOIN
state_streams b
WHERE
ABS(a.total_streams - b.total_streams) < 1000
AND
a.state <> b.state
Para información, las combinaciones cruzadas también se pueden escribir sin especificar explícitamente las combinaciones:SELECT
a.state as state_a,
b.state as state_b
FROM
state_streams a, state_streams b
WHERE
ABS(a.total_streams - b.total_streams) < 1000
AND
a.state <> b.state
Parte 2
Nota: esta es una pregunta adicional en lugar de una plantilla SQL realmente importante. ¡Puedes saltarte!Tarea: ¿cómo puedo modificar SQL de una solución anterior para eliminar duplicados? Por ejemplo, el ejemplo de la misma mesa, al vapor NC
y SC
solo hubo una vez, no dos.Decisión:SELECT
a.state as state_a,
b.state as state_b
FROM
state_streams a, state_streams b
WHERE
ABS(a.total_streams - b.total_streams) < 1000
AND
a.state > b.state
No. 3. Cálculos avanzados
Reconocimiento: esta tarea se adaptó de una discusión sobre una pregunta que hice en StackOverflow (mi apodo es zthomas.nc).Nota: esta es probablemente una tarea más difícil de lo que se le ofrecerá en una entrevista real. Piénselo más como un rompecabezas, ¡o puede omitirlo!Contexto: supongamos que tenemos una tabla de table
este tipo, donde user
diferentes valores de una clase pueden corresponder al mismo usuario class
:El | usuario | clase |
| ------ | ------- |
El | 1 | a |
El | 1 | b |
El | 1 | b |
El | 2 | b |
El | 3 | a |
Problema: suponga que solo hay dos valores posibles para una clase. Escriba una consulta para calcular el número de usuarios en cada clase. En este caso, los usuarios con ambas etiquetas a
y b
deben referirse a la clase b
.Para nuestra muestra, obtenemos el siguiente resultado:El | clase | contar |
| ------- | ------- |
El | a | 1 |
El | b | 2 |
Decisión:WITH usr_b_sum AS
(
SELECT
user,
SUM(CASE WHEN class = 'b' THEN 1 ELSE 0 END) num_b
FROM
table
GROUP BY
user
),
usr_class_label AS
(
SELECT
user,
CASE WHEN num_b > 0 THEN 'b' ELSE 'a' END class
FROM
usr_b_sum
)
SELECT
class,
COUNT(DISTINCT user) count
FROM
usr_class_label
GROUP BY
class
ORDER BY
class ASC
Una solución alternativa utiliza instrucciones SELECT
en los operadores SELECT
y UNION
:SELECT
"a" class,
COUNT(DISTINCT user_id) -
(SELECT COUNT(DISTINCT user_id) FROM table WHERE class = 'b') count
UNION
SELECT
"b" class,
(SELECT COUNT(DISTINCT user_id) FROM table WHERE class = 'b') count