Questions d'entretien SQL pour les professionnels expérimentés

Introduction

 
Si vous êtes un data scientist expérimenté à la recherche d'un emploi, vous n'auriez pas pu choisir un meilleur moment. À l'heure actuelle, de nombreuses organisations établies recherchent des scientifiques des données qui connaissent leur métier de fond en comble. Cependant, la forte demande ne signifie pas que vous pouvez ou devez sauter à travers les cerceaux et postuler à des postes de direction sans certaines compétences. Lorsqu'elles embauchent des data scientists expérimentés, les entreprises s'attendent à ce qu'elles travaillent sur les tâches les plus difficiles à accomplir. Ces employés doivent avoir une bonne compréhension des fonctionnalités les plus obscures afin de pouvoir les utiliser si nécessaire.

Il ne devrait pas être surprenant que lors des entretiens pour des postes de direction, les data scientists expérimentés aient tendance à se voir poser des questions beaucoup plus difficiles. Souvent, lorsqu'ils travaillent sur un seul travail pendant quelques années, les data scientists deviennent très compétents pour effectuer certaines tâches répétitives. Il est essentiel que les professionnels réalisent que SQL ne s'arrête pas et ne se termine pas avec leurs connaissances existantes. En ce qui concerne les concepts SQL avancés, il peut encore y avoir des lacunes dans leurs connaissances. Cela ne fait donc pas de mal d'obtenir de l'aide pour réussir votre entretien avec un data scientist. 

SQL est le langage principal pour la gestion des bases de données, donc effectuer des opérations SQL est au cœur du travail des data scientists. La plupart des entretiens avec des scientifiques des données sont organisés pour déterminer les connaissances d'un candidat en SQL. 

Le travail quotidien peut ne pas inclure la rédaction de requêtes complexes, mais vous devez montrer que si de telles compétences sont nécessaires, vous êtes la personne capable de le faire. Il ne devrait donc pas être surprenant que les enquêteurs demandent à un ensemble diversifié de Questions d'entretien SQL pour tester la maîtrise de SQL du candidat.

Dans cet article, nous voulions résumer certaines des questions et concepts complexes posés lors d'entretiens avec des professionnels expérimentés. Même si vous avez confiance en vos connaissances SQL, cela ne fait pas de mal d'analyser les mots-clés et de vous assurer que tout est couvert.

Concepts du strict minimum pour les professionnels expérimentés

CAS / QUAND

 
Une compréhension approfondie du concept de CASE (et de l'instruction When qui l'accompagne) est essentielle pour parvenir à une maîtrise complète de SQL. Une instruction case nous permet de vérifier certaines conditions et de renvoyer une valeur selon que ces conditions sont évaluées comme vraies ou fausses. En combinaison avec des clauses, telles que WHERE et ORDER BY, CASE nous permet d'introduire la logique, les conditions et l'ordre dans nos requêtes SQL.

La valeur des instructions CASE ne se limite pas à fournir une simple logique conditionnelle dans nos requêtes. Les data scientists expérimentés devraient avoir plus qu'une compréhension superficielle de la déclaration CASE et de ses utilisations. Les enquêteurs sont susceptibles de vous poser des questions sur différents types d'expressions CASE et sur la manière de les écrire. 

Les candidats expérimentés doivent être prêts à répondre à des questions théoriques, par exemple à expliquer les différences entre les déclarations CASE valorisées et recherchées, leur fonctionnement et leur rédaction. Cela nécessite une solide compréhension de leur syntaxe et de leurs pratiques courantes. Inutile de dire que cela inclut également l'utilisation appropriée de la clause ELSE.

Les data scientists expérimentés devront également savoir comment utiliser CASE avec des fonctions d'agrégation. On vous demandera peut-être également d'écrire une déclaration CASE abrégée, qui est moins répétitive et plus facile à comprendre. Vous devriez être capable de parler intelligemment des mises en garde et des risques possibles liés à l'utilisation d'instructions CASE abrégées.

En général, un data scientist expérimenté doit être capable d'utiliser CASE pour écrire des requêtes plus efficaces. Après tout, l'objectif de l'instruction CASE est d'éviter d'écrire trop de requêtes individuelles pour consolider les données.

Voici un exemple de la question qui peut être résolue à l'aide des instructions CASE/WHEN : https://platform.stratascratch.com/coding/9634-host-response-rates-with-cleaning-fees?python= 

C'est une question difficile posée dans les entretiens Airbnb, où les candidats doivent trouver le taux de réponse moyen de l'hôte, le code postal et les frais de nettoyage correspondants.

Dans ce cas, l'instruction CASE/WHEN est utilisée pour formater le résultat sous forme de nombre et le présenter sous forme de pourcentage, en plus du code postal.

SQL rejoint

 
Il est facile d'avoir confiance en vos connaissances sur les jointures SQL, mais plus vous explorez ce sujet, plus vous découvrirez que vous ne savez pas. Les intervieweurs demandent souvent questions d'entretien sur les aspects avancés des jointures SQL qui sont souvent négligés. Il est donc important d'approfondir ce concept et de le maîtriser à fond.
 
Outre les concepts de base, les enquêteurs peuvent demander ce que sont les jointures auto-croisées et découvrir la profondeur de vos connaissances en vous demandant de résoudre des questions pratiques. Vous devez connaître tous les différents types de jointures, y compris les types les plus complexes, tels que les jointures de hachage ou les jointures composites. On pourrait également vous demander d'expliquer ce que sont les jointures naturelles et quand elles sont les plus utiles. Parfois, vous devrez expliquer les différences entre les jointures naturelles et les jointures internes.
 
Vous devez, en général, avoir une expérience et une maîtrise approfondies de l'utilisation des jointures en combinaison avec d'autres instructions pour obtenir les résultats souhaités. Par exemple, vous devez savoir comment utiliser la clause WHERE pour utiliser la jointure croisée comme s'il s'agissait d'une jointure interne. Vous devrez également savoir utiliser les jointures pour produire de nouvelles tables sans mettre trop de pression sur le serveur. Ou comment utiliser des jointures externes pour identifier et remplir les valeurs manquantes lors de l'interrogation de la base de données. Ou le fonctionnement interne des jointures externes, comme le fait que la réorganisation de leur ordre peut modifier la sortie. 

Voici un exemple de la question qui implique la rédaction d'une déclaration conjointe interne. 

Il s'agit d'une question assez difficile, où les candidats sont invités à afficher la taille de la commande en pourcentage des dépenses totales.
 
 

Concept avancé N1 : Manipulation date-heure

Il est courant que les bases de données incluent des dates et des heures, donc tout data scientist expérimenté doit avoir une connaissance approfondie de leur utilisation. Ce type de données nous permet de suivre l'ordre dans lequel les événements se produisent, les changements de fréquence, de calculer les intervalles et d'obtenir d'autres informations importantes. La plupart du temps, effectuer ces opérations nécessite une maîtrise complète de la manipulation de la date et de l'heure en SQL. Ainsi, les professionnels possédant un tel ensemble de compétences auront un avantage sur les candidats concurrents. Si vous n'êtes pas sûr à 100 % de vos compétences, examinez les concepts décrits ci-dessous et voyez combien d'entre eux vous semblent familiers.

Comme il existe de nombreuses approches différentes (mais valides) pour formater les données en SQL, les grands codeurs devraient au moins les connaître toutes. Au cours des entretiens, les responsables du recrutement s'attendent à une connaissance des concepts de base du formatage des données et à la capacité de parler intelligemment du choix de la fonction appropriée pour la tâche. Cela inclut la connaissance d'une fonction FORMAT() importante et de la syntaxe associée pour utiliser pleinement la fonction. La connaissance d'autres fonctions de base, telles que NOW() est également attendue. En outre, il ne serait pas anodin que des professionnels expérimentés soient interrogés sur des concepts de base tels que les données de séries chronologiques et leur objectif.

Il est également important de tenir compte du contexte de l'emploi pour lequel vous postulez. Une entreprise d'IA ou d'IoT serait plus préoccupée par le suivi des données collectées à partir des capteurs, alors qu'une application de négociation d'actions peut vous obliger à suivre les fluctuations de prix au cours de la journée, de la semaine ou du mois.

Dans certains cas, les employeurs peuvent poser des questions sur des fonctions date/heure plus avancées dans SQL, telles que CAST(), EXTRACT() ou DATE_TRUNC(). Ces fonctions peuvent être précieuses lorsque vous travaillez avec un grand volume de données contenant des dates. Un data scientist expérimenté doit connaître le but de chaque fonction et ses applications. Dans un scénario idéal, il ou elle devrait avoir l'expérience de leur utilisation dans le passé.

La manipulation date-heure la plus complexe dans SQL va impliquer la combinaison de fonctions de base et avancées. Il est donc nécessaire de tous les connaître, en commençant par les plus basiques FORMAT(), NOW(),CURRENT_DATE et CURRENT_TIME, et en incluant les fonctions plus avancées mentionnées ci-dessus. En tant que data scientist expérimenté, vous devez également savoir ce que fait INTERVAL et quand l'utiliser.

Voici un exemple de question posée dans les entretiens Airbnb, où les candidats doivent utiliser les données disponibles pour suivre la croissance d'Airbnb.
 
 

La Prémisse :

 
Dans cette question, les candidats sont invités à suivre la croissance d'Airbnb en fonction de l'évolution du nombre d'hôtes inscrits chaque année. En d'autres termes, nous utiliserons le nombre d'hôtes nouvellement enregistrés comme indicateur de croissance pour chaque année. Nous trouverons le taux de croissance en calculant la différence du nombre d'hôtes entre l'année dernière et l'année en cours et en divisant ce nombre par le nombre d'hôtes enregistrés au cours de l'année précédente. Ensuite, nous trouverons la valeur en pourcentage en multipliant le résultat par 100.

Le tableau de sortie doit avoir des colonnes et les données correspondantes pour le nombre d'hôtes dans l'année en cours, dans l'année précédente, et le pourcentage de croissance d'année en année. Le pourcentage doit être arrondi au nombre entier le plus proche et les lignes doivent être classées par ordre croissant selon l'année.
 
 

Solution:

 
Pour répondre à cette question, un candidat doit travailler avec le tableau appelé 'airbnb_search_details', qui comprend de nombreuses colonnes. La colonne dont nous avons besoin est intitulée « hôte_depuis », qui indique l'année, le mois et le jour où l'hôte s'est inscrit pour la première fois sur le site Web. Pour cet exercice, le mois et le jour n'ont pas d'importance, la première chose à faire est donc d'extraire l'année de la valeur. Ensuite, nous devrons créer une vue qui comprend des colonnes distinctes pour l'année en cours, l'année précédente et le nombre total d'hôtes cette année-là. 

Sélectionnez      extraire (année FROM host_since::DATE) FROM airbnb_search_details OÙ host_since N'EST PAS NULL

Jusqu'à présent, nous avons fait deux choses :

1. Nous nous sommes assurés d'inclure uniquement les lignes où la colonne host_since n'est pas vide.
2. Nous avons extrait l'année des données et l'avons convertie en valeur DATE.

Sélectionnez       extract(year FROM host_since::DATE)       count(id) as current_year_host FROM airbnb_search_details WHERE host_since IS NOT NULL GROUP BY extract(year FROM host_since::DATE) ORDER BY year asc

Ensuite, nous comptons les identifiants et configurons la clause GROUP BY pour chaque année. Et faites-le afficher dans l'ordre croissant. 

Cela devrait nous donner un tableau avec deux colonnes : l'année et le nombre d'hôtes enregistrés cette année-là. Nous n'avons toujours pas une image complète nécessaire pour résoudre la question, mais c'est un pas dans la bonne direction. Nous avons également besoin de colonnes séparées pour les hôtes inscrits au cours de l'année précédente. C'est là qu'intervient la fonction LAG().

Sélectionnez l'année, current_year_host, lag (current_year_host, 1) sur (ordre par an) comme prév_year_host sélectionnez Extract (année dans host_sece :: date) compter (id) comme current_year_host dans Airbnb_search_details où host_sence n'est pas un groupe null par extrait (année depuis host_sence: :DATE) ORDRE PAR année asc

Ici, nous avons ajouté la troisième colonne, qui sera intitulée 'prev_year_host' et ses valeurs proviendront de 'current_year_host', à l'exception du retard d'une ligne. Voici à quoi cela pourrait ressembler :

Disposer le tableau de cette manière rend très pratique le calcul du taux de croissance final. Nous avons une colonne séparée pour chaque valeur de l'équation. En fin de compte, notre code devrait ressembler à ceci :

SELECT année,       current_year_host,       prev_year_host,       round(((current_year_host - prev_year_host)/(cast(prev_year_host AS numeric)))*100)estimate_growth FROM   (SELECT année,           current_year_host,           LAG(current_year_host, 1) OVER (ORDER BY year) AS prev_year_host FROM     (SELECT extrait(année                     FROM host_since::date) AS année,             count(id) current_year_host       FROM airbnb_search_details       WHERE host_since IS NOT NULL       GROUP BY extract(year                      FROM host_since::date)       ORDER BY année) t1) t2

Ici, nous ajoutons une autre requête et une autre colonne où nous calculons le taux de croissance. Nous devons multiplier le résultat initial par 100 et l'arrondir pour satisfaire aux exigences de la tâche. 

C'est la solution à cette tâche. Il est clair que les fonctions de manipulation date-heure étaient essentielles pour accomplir la tâche. 
 
 

Concept Avancé N2 : Fonctions de Fenêtre et Partitions

Fonctions de fenêtre SQL sont l'un des concepts les plus importants pour l'écriture de requêtes SQL complexes mais efficaces. Les professionnels expérimentés doivent avoir une connaissance pratique et théorique approfondie des fonctions des fenêtres. Cela inclut de savoir ce qu'est la clause over et de maîtriser son utilisation. Les enquêteurs peuvent se demander comment la clause OVER peut transformer des fonctions d'agrégat en fonctions de fenêtre. Vous pourriez également être interrogé sur les trois fonctions d'agrégat qui peuvent être utilisées comme fonctions de fenêtre. Les data scientists expérimentés doivent également connaître les autres fonctions de fenêtre non agrégées.

Pour tirer le meilleur parti des fonctions de fenêtre, il faut aussi savoir ce qu'est la clause PARTITION BY et comment l'utiliser. Il peut vous être demandé de l'expliquer et de fournir des exemples de quelques cas d'utilisation. Parfois, vous devrez organiser les lignes au sein des partitions à l'aide de la clause ORDER_BY.

Les candidats qui peuvent démontrer une connaissance approfondie de chaque fonction de fenêtre individuelle, comme ROW_NUMBER(), auront un avantage. Inutile de dire que les connaissances théoriques seules ne suffisent pas – les professionnels doivent également avoir l'expérience de leur utilisation en pratique, avec ou sans cloisons. Par exemple, un professionnel expérimenté devrait être capable d'expliquer les différences entre RANK() et DENSE_RANK(). Un candidat idéal doit connaître certains des concepts les plus avancés, tels que les cadres dans les partitions, et être capable de les expliquer clairement.

Les bons candidats doivent également expliquer l'utilisation de la fonction NTH_VALUE(). Cela ne ferait pas de mal de mentionner les alternatives à cette fonction, telles que les fonctions FIRST_VALUE() et LAST_VALUE(). Les entreprises aiment souvent mesurer les quartiles, les quantiles et les centiles en général. Pour effectuer cette opération, les data scientists doivent également savoir utiliser la fonction de fenêtre NTILE().

En SQL, il existe généralement de nombreuses façons d'aborder une tâche. Néanmoins, les fonctions de fenêtre constituent le moyen le plus simple d'effectuer des opérations courantes mais complexes. Un bon exemple d'une telle fonction de fenêtre est LAG() ou LEAD(), vous devez donc les connaître également. Par exemple, regardons un exemple de la solution précédente à une question d'entretien Airbnb difficile :

Pour afficher le nombre d'hôtes de l'année précédente, nous avons utilisé la fonction LAG() avec l'instruction OVER. Cela aurait pu être fait de bien d'autres manières, mais les fonctions de fenêtre nous ont permis d'obtenir le résultat souhaité en une seule ligne de code SQL :

LAG(current_year_host, 1) OVER (ORDER BY year) en tant que prev_year_host

De nombreuses entreprises doivent calculer la croissance sur une certaine période de temps. La fonction LAG() peut être inestimable pour effectuer de telles affectations.
 
 

Concept avancé N3 : Croissance d'un mois à l'autre

De nombreuses organisations utilisent l'analyse des données pour mesurer leur propre performance. Cela peut impliquer de mesurer l'efficacité des campagnes marketing ou le retour sur investissement sur un investissement spécifique. La réalisation d'une telle analyse nécessite une connaissance approfondie de SQL, comme la date, l'heure et les fonctions de fenêtre.

Les data scientists devront également prouver leurs compétences dans la mise en forme des données et leur affichage en pourcentage ou sous toute autre forme. En général, pour résoudre les questions pratiques où vous devez calculer la croissance d'un mois à l'autre, vous devez utiliser la combinaison de plusieurs ensembles de compétences. Certains des concepts requis seront avancés (fonctions de fenêtre, manipulation date-heure), tandis que d'autres seront basiques (fonctions d'agrégat et instructions SQL courantes).

Examinons un exemple de question posée par les enquêteurs d'Amazon.

La Prémisse :

 
Dans cette question, nous devons travailler avec un tableau d'achats et calculer la croissance ou la baisse mensuelle des revenus. Le résultat final doit être formaté d'une manière spécifique (format AAAA-MM) et les pourcentages doivent être arrondis à la deuxième décimale la plus proche. 

Solution:

 
Lorsque vous travaillez sur une tâche comme celle-ci, la première chose que vous devez faire est de comprendre le tableau. Vous devez également identifier les colonnes avec lesquelles vous devez travailler pour répondre à la question. Et à quoi ressemblera votre sortie.

Dans notre exemple, les valeurs de données ont le type d'objet, nous devrons donc utiliser la fonction CAST() pour les transformer en types de date.

SELECT to_char(cast(created_at as date), 'YYYY-MM') FROM sf_transactions

La question spécifie également un format pour les dates, nous pouvons donc utiliser la fonction TO_CHAR() dans SQL pour afficher la date dans ce format.

Pour calculer la croissance, nous devons également sélectionner la fonction d'agrégat created_at et SUM() pour obtenir le volume des ventes totales pour cette date. 

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

À ce stade, nous devons à nouveau utiliser les fonctions de fenêtre. Plus précisément, nous allons utiliser la fonction LAG() pour accéder au volume du mois dernier et l'afficher dans une colonne distincte. Pour cela, nous aurons également besoin d'une clause OVER.

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

Sur la base du code que nous avons écrit jusqu'à présent, notre tableau ressemblera à ceci :

Ici, nous avons les dates et les valeurs totales correspondantes dans la colonne somme, et les valeurs de la dernière date dans la colonne décalage. Nous pouvons maintenant insérer les valeurs dans la formule et afficher le taux de croissance dans une colonne distincte.

Nous devons également supprimer la colonne created_at inutile et remplacer les clauses GROUP BY et ORDER BY par 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 année mois

Une fois le code exécuté, notre tableau ne doit inclure que les colonnes essentielles à notre calcul.

Maintenant, nous pouvons enfin arriver à la solution. Voici à quoi ressemblerait le code final :

SELECT to_char(created_at::date, 'YYYY-MM') AS année_mois,       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 année_mois WINDOW w AS (         ORDER BY to_char(created_at::date, 'YYYY-MM')) ORDER BY année_mois ASC

Dans ce code, nous prenons deux valeurs de colonne de l'exemple précédent et calculons la différence entre elles. Notez que nous utilisons également des alias de fenêtre pour réduire la répétitivité de notre code.

Ensuite, conformément à l'algorithme, nous le divisons par le revenu du mois en cours et le multiplions par 100 pour obtenir la valeur en pourcentage. Enfin, nous arrondissons la valeur en pourcentage à deux décimales. Nous arrivons à la réponse qui satisfait toutes les exigences de la tâche. 

Concept avancé N4 : taux de désabonnement

 
Même si c'est le contraire de la croissance, le taux de désabonnement est également une mesure importante. De nombreuses entreprises gardent une trace de leurs taux de désabonnement, surtout si leur modèle commercial est basé sur un abonnement. De cette façon, ils peuvent suivre le nombre d'abonnements ou de comptes perdus et prédire les raisons qui l'ont causé. Un data scientist expérimenté devra savoir quelles fonctions, déclarations et clauses utiliser pour calculer les taux de désabonnement.

Les données d'abonnement sont très privées et contiennent des informations d'utilisateur privées. Il est également important que les data scientists sachent comment travailler avec de telles données sans les exposer. Le calcul des taux de désabonnement implique souvent des expressions de table communes, qui sont un concept relativement nouveau. Les meilleurs data scientists devraient savoir pourquoi les CTE sont utiles et quand les utiliser. Lorsque vous travaillez avec des bases de données plus anciennes, où les CTE ne sont pas disponibles, un candidat idéal devrait toujours être capable de faire le travail.

Voici un exemple de tâche difficile. Les candidats qui passent un entretien chez Lyft reçoivent cette mission pour calculer le taux de désabonnement des chauffeurs dans l'entreprise.

Pour résoudre ce problème, les scientifiques des données doivent utiliser des instructions case/when, des fonctions de fenêtre telles que LAG(), ainsi que FROM / WHERE et d'autres clauses de base. 

Conclusion

 
Travailler en tant que data scientist pendant de nombreuses années est certainement impressionnant sur un CV et vous obtiendrez de nombreux entretiens. Cependant, une fois que vous avez mis le pied dans la porte, vous devez toujours afficher les connaissances nécessaires pour compléter des années d'expérience. Même si vous avez une vaste expérience dans écriture de requêtes en SQL, cela ne fait pas de mal d'utiliser des ressources comme StrataScratch pour rafraîchir vos connaissances.

 
 
Nate Rosidi est data scientist et en stratégie produit. Il est également professeur adjoint enseignant l'analytique et fondateur de StrataScratch, une plate-forme aidant les data scientists à préparer leurs entretiens avec de vraies questions d'entretien posées par les meilleures entreprises. Connectez-vous avec lui sur Twitter : StrataScratch or LinkedIn.

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