Preguntas de entrevista de SQL para profesionales experimentados

Introducción

 
Si es un científico de datos experimentado que busca trabajo, no podría haber elegido un mejor momento. En este momento, muchas organizaciones establecidas están buscando científicos de datos que conozcan su oficio por dentro y por fuera. Sin embargo, la alta demanda no significa que pueda, o deba pasar por el aro y postularse para puestos de alto nivel sin un determinado conjunto de habilidades. Al contratar científicos de datos con experiencia, las empresas esperan que trabajen en las tareas más difíciles que tienen entre manos. Estos empleados deben tener un buen conocimiento incluso de las funciones más oscuras para que puedan usarlas cuando sea necesario.

No debería sorprender que al realizar entrevistas para puestos de alto nivel, los científicos de datos experimentados tiendan a recibir preguntas mucho más difíciles. A menudo, cuando se trabaja en un solo trabajo durante un par de años, los científicos de datos se vuelven muy hábiles para realizar ciertas tareas repetitivas. Es esencial que los profesionales se den cuenta de que SQL no se detiene y termina con su conocimiento existente. Cuando se trata de conceptos avanzados de SQL, aún puede haber algunas lagunas en su conocimiento. Por lo tanto, no está de más recibir ayuda para tener éxito en su entrevista con el científico de datos. 

SQL es el lenguaje principal para administrar bases de datos, por lo que realizar operaciones de SQL es el núcleo del trabajo de los científicos de datos. La mayoría de las entrevistas a científicos de datos se organizan para determinar el conocimiento de SQL de un candidato. 

Es posible que el trabajo diario no incluya escribir consultas complejas, pero debe demostrar que si se necesitan tales habilidades, usted es la persona capaz de hacerlo. Por lo tanto, no debería sorprender que los entrevistadores pregunten a un conjunto diverso de Preguntas de la entrevista SQL para probar la fluidez del candidato en SQL.

En este artículo, queríamos resumir algunas de las preguntas y conceptos complejos planteados en entrevistas con profesionales experimentados. Incluso si confía en su conocimiento de SQL, no está de más escanear las palabras clave y asegurarse de tener todo cubierto.

Conceptos mínimos desnudos para profesionales experimentados

CASO / CUANDO

 
Comprender a fondo el concepto de CASE (y la instrucción When que lo acompaña) es esencial para lograr un dominio completo de SQL. Una declaración de caso nos permite verificar ciertas condiciones y devolver un valor en función de si esas condiciones se evalúan como verdaderas o falsas. En combinación con cláusulas, como WHERE y ORDER BY, CASE nos permite incorporar lógica, condiciones y orden en nuestras consultas SQL.

El valor de las declaraciones CASE no se limita a proporcionar una lógica condicional simple en nuestras consultas. Los científicos de datos experimentados deben tener una comprensión más que superficial de la declaración CASE y sus usos. Es probable que los entrevistadores le hagan preguntas sobre los diferentes tipos de expresiones CASE y cómo escribirlas. 

Los candidatos experimentados deben estar preparados para responder preguntas teóricas, como explicar las diferencias entre declaraciones CASE valoradas y buscadas, cómo funcionan y cómo escribirlas. Esto requiere una sólida comprensión de su sintaxis y prácticas comunes. No hace falta decir que esto también incluye el uso adecuado de la cláusula ELSE.

Se espera que los científicos de datos experimentados también sepan cómo usar CASE con funciones agregadas. También es posible que le pidan que escriba una declaración CASE abreviada, que es menos repetitiva y más fácil de entender. Debería poder hablar de manera inteligente sobre las advertencias y los posibles riesgos de usar declaraciones CASE abreviadas.

En general, un científico de datos experimentado debe poder usar CASE para escribir consultas más eficientes. Después de todo, el propósito completo de la instrucción CASE es evitar escribir demasiadas consultas individuales para consolidar los datos.

Aquí hay un ejemplo de la pregunta que se puede resolver usando declaraciones CASE / WHEN: https://platform.stratascratch.com/coding/9634-host-response-rates-with-cleaning-fees?python= 

Esta es una pregunta difícil que se hace en las entrevistas de Airbnb, donde los candidatos deben encontrar la tasa de respuesta promedio del anfitrión, el código postal y su tarifa de limpieza correspondiente.

En este caso, la declaración CASE/WHHEN se usa para formatear el resultado como un número y presentarlo como un valor porcentual, además del código postal.

SQL se une

 
Es fácil confiar en su conocimiento de SQL Joins, pero cuanto más explore este tema, más descubrirá que no sabe. Los entrevistadores preguntan a menudo preguntas de la entrevista sobre aspectos avanzados de SQL Joins que a menudo se pasan por alto. Por eso es importante profundizar en este concepto y dominarlo a fondo.
 
Además de los conceptos básicos, los entrevistadores pueden indagar qué son las autouniones cruzadas y descubrir la profundidad de su conocimiento preguntando para resolver preguntas prácticas. Debe conocer todos los diferentes tipos de combinaciones, incluidos los tipos más complejos, como combinaciones hash o combinaciones compuestas. También se le puede pedir que explique qué son las uniones naturales y cuándo son más útiles. A veces tendrás que explicar las diferencias entre uniones naturales e internas.
 
En general, debe tener una experiencia y un dominio completos del uso de combinaciones en combinación con otras declaraciones para lograr los resultados deseados. Por ejemplo, debe saber cómo usar la cláusula WHERE para utilizar Cross Join como si fuera un Inner Join. También se espera que sepa cómo utilizar combinaciones para producir nuevas tablas sin ejercer demasiada presión sobre el servidor. O cómo usar combinaciones externas para identificar y completar los valores faltantes al consultar la base de datos. O el funcionamiento interno de las uniones externas, como el hecho de que reorganizar su orden puede cambiar la salida. 

Aquí hay un ejemplo de la pregunta que implica escribir una declaración conjunta interna. 

Esta es una pregunta bastante difícil, en la que se pide a los candidatos que muestren el tamaño del pedido como porcentaje del gasto total.
 
 

Concepto Avanzado N1: Manipulación de fecha y hora

Es común que las bases de datos incluyan fechas y horas, por lo que cualquier científico de datos experimentado debe tener un conocimiento profundo para trabajar con ellas. Este tipo de datos nos permite rastrear el orden en que ocurren los eventos, cambios en la frecuencia, calcular intervalos y obtener otros conocimientos importantes. Muchas veces, realizar estas operaciones requiere un dominio completo de la manipulación de fecha y hora en SQL. Entonces, los profesionales con tal conjunto de habilidades tendrán una ventaja sobre los candidatos competidores. Si no está 100% seguro de sus habilidades, revise los conceptos que se describen a continuación y vea cuántos de ellos le suenan familiares.

Dado que existen muchos enfoques diferentes (pero válidos) para formatear datos en SQL, los grandes codificadores deberían al menos estar familiarizados con todos ellos. Durante las entrevistas, los gerentes de contratación esperan conocer los conceptos básicos de formato de datos y la capacidad de hablar inteligentemente sobre la elección de la función adecuada para la tarea. Esto incluye el conocimiento de una función FORMAT() importante y la sintaxis asociada para hacer un uso completo de la función. También se espera el conocimiento de otras funciones básicas, como NOW(). Además, no sería inesperado que a los profesionales experimentados se les preguntara sobre conceptos básicos como datos de series temporales y su propósito.

También es importante considerar el contexto del trabajo que está solicitando. Una empresa de IA o IoT estaría más preocupada por rastrear los datos recopilados de los sensores, mientras que una aplicación de comercio de acciones podría requerir que rastree las fluctuaciones de precios durante el día, la semana o el mes.

En algunos casos, los empleadores pueden preguntar sobre funciones de fecha/hora más avanzadas en SQL, como CAST(), EXTRACT() o DATE_TRUNC(). Estas funciones pueden ser invaluables cuando trabaja con un gran volumen de datos que contienen fechas. Un científico de datos experimentado debe conocer el propósito de cada función y sus aplicaciones. En un escenario ideal, él o ella debería tener experiencia en su uso en el pasado.

La manipulación de fecha y hora más compleja en SQL implicará la combinación de funciones básicas y avanzadas. Por lo tanto, es necesario conocerlos todos, comenzando por los más básicos FORMAT (), NOW (), CURRENT_DATE y CURRENT_TIME, e incluyendo las funciones más avanzadas mencionadas anteriormente. Como científico de datos experimentado, también debe saber qué hace INTERVAL y cuándo usarlo.

Aquí hay un ejemplo de una pregunta hecha en las entrevistas de Airbnb., donde los candidatos deben usar los datos disponibles para rastrear el crecimiento de Airbnb.
 
 

La premisa:

 
En esta pregunta, se les pide a los candidatos que sigan el crecimiento de Airbnb en función de los cambios en la cantidad de anfitriones registrados cada año. En otras palabras, usaremos la cantidad de hosts nuevos registrados como indicador de crecimiento para cada año. Encontraremos la tasa de crecimiento calculando la diferencia en el número de hosts entre el año anterior y el actual y dividiendo ese número por el número de hosts registrados durante el año anterior. Luego, encontraremos el valor porcentual multiplicando el resultado por 100.

La tabla de salida debe tener columnas y los datos correspondientes para el número de hosts en el año actual, en el año anterior y el porcentaje de crecimiento de un año a otro. El porcentaje debe redondearse al número entero más cercano y las filas deben ordenarse en orden ascendente según el año.
 
 

Solución:

 
Para responder a esta pregunta, un candidato debe trabajar con la tabla llamada 'airbnb_search_details', que incluye muchas columnas. La columna que necesitamos está etiquetada como 'host_since', que indica el año, el mes y el día en que el host se registró por primera vez en el sitio web. Para este ejercicio, el mes y el día son irrelevantes, por lo que lo primero que debemos hacer es extraer el año del valor. Luego tendremos que crear una vista que incluya columnas separadas para el año actual, el año anterior y el número total de hosts en ese año. 

Seleccione extraer (año DESDE host_since::FECHA) DESDE airbnb_search_detalles DONDE host_since NO ES NULO

Hasta ahora hemos hecho dos cosas:

1. Nos hemos asegurado de incluir solo las filas donde la columna host_since no está vacía.
2. Hemos extraído el año de los datos y lo convertimos en el valor de FECHA.

Seleccione extract(year FROM host_since::DATE) count(id) as current_year_host FROM airbnb_search_details DONDE host_since NO ES NULL GROUP BY extract(year FROM host_since::DATE) ORDER BY year asc

Luego procedemos a contar los ids y configuramos la cláusula GROUP BY para cada año. Y haz que se muestre en orden ascendente. 

Esto debería darnos una tabla con dos columnas: el año y el número de hosts registrados ese año. Todavía no tenemos una imagen completa necesaria para resolver la pregunta, pero es un paso en la dirección correcta. También necesitamos columnas separadas para los hosts registrados durante el año anterior. Aquí es donde entra la función LAG ().

SELECCIONE Year, current_year_host, LAG(current_year_host, 1) OVER (ORDEN BY year) as prev_year_host Select extract(year FROM host_since::DATE) count(id) as current_year_host FROM airbnb_search_details DONDE host_since NO ES NULL GROUP BY extract(year FROM host_since: :FECHA) ORDEN POR año asc

Aquí, agregamos la tercera columna, que se etiquetará como 'prev_year_host' y sus valores vendrán de 'current_year_host', excepto por el retraso de una fila. Así es como se vería eso:

Organizar la tabla de esta manera hace que sea muy conveniente calcular la tasa de crecimiento final. Tenemos una columna separada para cada valor de la ecuación. En última instancia, nuestro código debería verse así:

SELECCIONE año, host_año_actual, host_año_anterior, ronda(((host_año_actual - host_año_anterior)/(cast(anfitrión_año_anterior AS numérico)))*100) crecimiento_estimado DESDE (SELECCIONE año, host_año_actual, LAG(host_año_actual, 1) SOBRE (ORDENAR POR año) AS host_año_anterior DESDE (SELECCIONE extracto(año DESDE host_desde::fecha) AS año, recuento(id) año_actual_host DESDE airbnb_search_detalles DONDE host_since NO ES NULO GRUPO POR extracto(año DESDE host_desde::fecha) ORDEN POR año) t1) t2

Aquí, agregamos otra consulta y otra columna donde calculamos la tasa de crecimiento. Debemos multiplicar el resultado inicial por 100 y redondearlo para satisfacer los requisitos de la tarea. 

Esa es la solución a esta tarea. Está claro que las funciones de manipulación de fecha y hora eran esenciales para completar la tarea. 
 
 

Concepto avanzado N2: funciones de ventana y particiones

Funciones de la ventana SQL son uno de los conceptos más importantes para escribir consultas SQL complejas pero eficientes. Se espera que los profesionales experimentados tengan un profundo conocimiento práctico y teórico de las funciones de las ventanas. Esto incluye saber qué es la cláusula over y dominar su uso. Los entrevistadores podrían preguntarse cómo la cláusula OVER puede convertir funciones agregadas en funciones de ventana. También es posible que le pregunten acerca de las tres funciones agregadas que se pueden usar como funciones de ventana. Los científicos de datos experimentados también deben conocer otras funciones de ventana no agregadas.

Para hacer el mejor uso de las funciones de la ventana, también se debe saber qué es la cláusula PARTITION BY y cómo usarla. Es posible que se le pida que lo explique y proporcione ejemplos de algunos casos de uso. A veces tendrás que organizar filas dentro de particiones usando la cláusula ORDER_BY.

Los candidatos que puedan demostrar un conocimiento profundo de cada función de ventana individual, como ROW_NUMBER() tendrán una ventaja. No hace falta decir que el conocimiento teórico por sí solo no es suficiente: los profesionales también deben tener experiencia en su uso en la práctica, con o sin mamparas. Por ejemplo, un profesional experimentado debería poder explicar las diferencias entre RANK() y DENSE_RANK(). Un candidato ideal debe conocer algunos de los conceptos más avanzados, como los marcos dentro de las particiones, y ser capaz de explicarlos con claridad.

Los grandes candidatos también deberían explicar el uso de la función NTH_VALUE (). No estaría de más mencionar las alternativas a esta función, como las funciones FIRST_VALUE () y LAST_VALUE (). A las empresas a menudo les gusta medir cuartiles, cuantiles y percentiles en general. Para realizar esta operación, los científicos de datos también deben saber cómo usar la función de ventana NTILE ().

En SQL, generalmente hay muchas formas de abordar una tarea. Aún así, las funciones de ventana proporcionan la forma más fácil de realizar operaciones comunes pero complejas. Un buen ejemplo de una función de ventana de este tipo es LAG() o LEAD(), por lo que también debería estar familiarizado con ellas. Por ejemplo, veamos un ejemplo de la solución anterior a una pregunta difícil de una entrevista en Airbnb:

Para mostrar la cantidad de hosts en el año anterior, usamos la función LAG() con la instrucción OVER. Esto podría haberse hecho de muchas otras formas, pero las funciones de ventana nos permitieron obtener el resultado deseado en solo una línea de código SQL:

LAG (current_year_host, 1) OVER (ORDER BY year) como prev_year_host

Muchas empresas necesitan calcular el crecimiento durante un cierto período de tiempo. La función LAG () puede ser invaluable para completar tales asignaciones.
 
 

Concepto Avanzado N3: Crecimiento Mes a Mes

Muchas organizaciones utilizan el análisis de datos para medir su propio desempeño. Esto puede implicar medir la efectividad de las campañas de marketing o el ROI en una inversión específica. La realización de dicho análisis requiere un conocimiento profundo de SQL, como la fecha, la hora y las funciones de ventana.

Los científicos de datos también deberán demostrar sus habilidades para formatear los datos y mostrarlos como porcentajes o en cualquier otra forma. En general, para resolver las preguntas prácticas en las que debe calcular el crecimiento mes a mes, debe usar la combinación de múltiples conjuntos de habilidades. Algunos de los conceptos requeridos serán avanzados (funciones de ventana, manipulación de fecha y hora), mientras que otros serán básicos (funciones agregadas y declaraciones SQL comunes).

Veamos una pregunta de ejemplo hecha por los entrevistadores en Amazon.

La premisa:

 
En esta pregunta, tenemos que trabajar con una tabla de compras y calcular el crecimiento o la disminución mensual de los ingresos. El resultado final debe formatearse de una manera específica (formato AAAA-MM) y los porcentajes deben redondearse al segundo decimal más cercano. 

Solución:

 
Cuando trabaje en una tarea como esta, lo primero que debe hacer es comprender la tabla. También debe identificar las columnas con las que necesita trabajar para responder la pregunta. Y cómo se verá su salida.

En nuestro ejemplo, los valores de datos tienen el tipo de objeto, por lo que tendremos que usar la función CAST() para transformarlos en tipos de fecha.

SELECCIONE to_char(cast(created_at as date), 'AAAA-MM') DESDE sf_transactions

La pregunta también especifica un formato para las fechas, por lo que podemos usar la función TO_CHAR() en SQL para generar la fecha en este formato.

Para calcular el crecimiento, también debemos seleccionar la función agregada created_at y SUM () para obtener el volumen de ventas totales para esa fecha. 

SELECCIONE to_char(cast(created_at as date), 'YYYY-MM'), created_at, sum(value) FROM sf_transactions

En este punto, tenemos que usar las funciones de ventana nuevamente. Específicamente, usaremos la función LAG () para acceder al volumen del mes pasado y mostrarlo como una columna separada. Para eso, también necesitaremos una cláusula OVER.

SELECT to_char(cast(created_at as date), 'YYYY-MM') AS year_month, created_at, sum(value) lag(sum(value), 1) OVER (ORDER BY created_at::date) FROM sf_transactions GROUP BY created_at

Según el código que hemos escrito hasta ahora, nuestra tabla se verá así:

Aquí tenemos las fechas y los valores totales correspondientes en la columna de suma, y ​​los valores de la última fecha en la columna de retraso. Ahora podemos introducir los valores en la fórmula y mostrar la tasa de crecimiento en una columna separada.

También debemos eliminar la columna created_at innecesaria y cambiar las cláusulas GROUP BY y ORDER BY a year_month.

SELECT to_char(cast(created_at as date), 'YYYY-MM') AS year_month, sum(value), lag(sum(value), 1) OVER (ORDER BY to_char(cast(created_at as date)) FROM sf_transactions GROUP BY año mes

Una vez que ejecutamos el código, nuestra tabla solo debe incluir las columnas que son esenciales para nuestro cálculo.

Ahora finalmente podemos llegar a la solución. Así es como se vería el código final:

SELECT to_char(created_at::date, 'YYYY-MM') AS year_month, round(((sum(value) - lag(sum(value), 1) OVER w) / (lag(sum(value), 1) OVER w)) * 100, 2) AS Revenue_diff_pct FROM sf_transactions GROUP BY year_month WINDOW AS ( ORDER BY to_char(created_at::date, 'YYYY-MM')) ORDER BY year_month ASC

En este código, tomamos dos valores de columna del ejemplo anterior y calculamos la diferencia entre ellos. Tenga en cuenta que también utilizamos alias de ventana para reducir la repetitividad de nuestro código.

Luego, de acuerdo con el algoritmo, lo dividimos por los ingresos del mes actual y lo multiplicamos por 100 para obtener el valor porcentual. Finalmente, redondeamos el valor porcentual a dos decimales. Llegamos a la respuesta que satisface todos los requisitos de la tarea. 

Concepto Avanzado N4: Tasas de Churn

 
Aunque es lo opuesto al crecimiento, la rotación también es una métrica importante. Muchas empresas realizan un seguimiento de sus tasas de abandono, especialmente si su modelo de negocio se basa en suscripciones. De esta manera, pueden rastrear la cantidad de suscripciones o cuentas perdidas y predecir las razones que lo causaron. Se espera que un científico de datos experimentado sepa qué funciones, declaraciones y cláusulas usar para calcular las tasas de abandono.

Los datos de suscripción son muy privados y contienen información privada del usuario. También es importante que los científicos de datos sepan cómo trabajar con esos datos sin exponerlos. A menudo, el cálculo de las tasas de abandono implica expresiones de tabla comunes, que son un concepto relativamente nuevo. Los mejores científicos de datos deben saber por qué los CTE son útiles y cuándo usarlos. Cuando se trabaja con bases de datos más antiguas, donde los CTE no están disponibles, un candidato ideal aún debería poder hacer el trabajo.

Aquí hay un ejemplo de una tarea difícil.. Los candidatos que se entrevistan en Lyft reciben esta tarea para calcular la tasa de abandono de los conductores en la empresa.

Para resolver este problema, los científicos de datos deben usar declaraciones case/when, funciones de ventana como LAG(), así como FROM/WHERE y otras cláusulas básicas. 

Conclusión

 
Trabajar como científico de datos durante muchos años ciertamente se ve impresionante en un CV y ​​le dará muchas entrevistas. Sin embargo, una vez que pone el pie en la puerta, aún necesita mostrar el conocimiento para complementar años de experiencia. Incluso si tiene una amplia experiencia en escribiendo consultas en SQL, no está de más utilizar recursos como StrataScratch para refrescar sus conocimientos.

 
 
Nate Rosidi es científico de datos y en estrategia de producto. También es profesor adjunto de enseñanza de análisis y es el fundador de StrataScratch, una plataforma que ayuda a los científicos de datos a prepararse para sus entrevistas con preguntas de entrevistas reales de las principales empresas. Conéctate con él en Gorjeo: StrataScratch or Etiqueta LinkedIn.

Fuente: https://www.kdnuggets.com/2022/01/sql-interview-questions-experienced-professionals.html