Questions d'entretien Amazon SQL

Vous vous préparez à un entretien chez Amazon ? Jetez un coup d'œil à ces questions d'entretien et vérifiez vos connaissances professionnelles en SQL !

La préparation d'un entretien pour une grande entreprise telle qu'Amazon peut s'avérer fastidieuse, mais vous pouvez toujours vous entraîner à l'avance et renforcer vos compétences en SQL ! Dans cet article, nous allons passer en revue les questions d'entretien SQL d'Amazon et vous aider à vous préparer pour votre prochain grand entretien. Vous souhaitez maîtriser l'ensemble du langage SQL et réussir n'importe quelle tâche ? Notre piste complète Le SQL de A à Z, où nous couvrons chaque coin de SQL avec plus de 850 exercices, vous aidera à atteindre votre objectif.

Si vous cherchez d'autres questions d'entretien d'embauche en SQL, consultez notre article 100+ questions d'entretien et exercices d'entraînement en SQL. Nous y avons rassemblé de nombreux autres exercices SQL pour vous aider à préparer votre prochain entretien.

Comment se déroule un entretien SQL chez Amazon ?

Vous pouvez postuler à un poste chez Amazon sur amazon.jobs et utiliser la fonction de recherche pour trouver toutes les offres liées à SQL. Vous pouvez également rechercher des mots-clés connexes tels que "base de données", "science des données" ou "analyste" pour trouver des postes similaires.

Si votre candidature est retenue et que votre CV est pertinent, un recruteur vous contactera pour un rapide entretien téléphonique. Ce premier appel ne sera probablement pas technique ; il s'agira de déterminer si vous correspondez bien à la culture de l'entreprise. Une fois que vous avez passé cette étape, l'entretien technique commence.

Les enquêteurs techniques d'Amazon vous poseront des questions relatives au langage SQL et aux données, ainsi que d'autres questions spécifiques au poste pour lequel vous avez postulé. Les entretiens SQL d'Amazon sont réputés pour couvrir d'abord les bases de la syntaxe, puis pour plonger dans les JOIN complexes et les fonctions de fenêtre. Vous pouvez vous entraîner sur ces sujets grâce à notre cours Les jointures en SQL et à notre cours complet sur les fonctions de fenêtre .

Dans cet article, nous nous concentrerons sur la pratique des JOIN et des fonctions de fenêtre. Si vous souhaitez rafraîchir vos connaissances sur les bases du langage SQL, consultez notre liste de questions d'entretien courantes sur le langage SQL.

Connaître les données

Pour cet exercice, nous utiliserons deux tables simples, salesperson et product. Examinons chacune d'entre elles.

La table salesperson stocke des informations sur tous les représentants commerciaux de l'entreprise. Chacun est identifié par un id (stocké sous la forme d'un type de données INT) et possède un name (TEXT), un salary (NUMERIC) et un manager_id (INT) facultatif. Lorsqu'il est présent, l'ID du manager pointe vers le site id d'un autre représentant commercial qui supervise le vendeur actuel.

La table product contient des informations sur les produits vendus par les vendeurs. Chaque produit est identifié par son id (INT) et possède un name (TEXT), un price (NUMERIC), un category (TEXT) et un salesperson_id (INT) obligatoire. Ce dernier champ indique l'identifiant du représentant qui vend le produit.

Maintenant que nous avons examiné les données, commençons les exercices. Il peut être utile d'avoir nos antisèches SQL gratuites. Nos antisèchesLes jointures en SQL et Fonctions de fenêtrage vous aideront si vous avez besoin d'un rappel rapide de la syntaxe d'une commande.

Questions d'entretien SQL Amazon, réponses et explications

1. représentants commerciaux à hauts revenus

Tâche : Trouvez tous les vendeurs dont le salaire est supérieur à celui de leurs responsables.

Réponse :

SELECT e1.name
FROM salesperson e1
JOIN salesperson e2
  ON e1.manager_id = e2.id
  AND e2.salary < e1.salary;

Explication :

Pour associer chaque vendeur à son directeur, nous devons utiliser une jointure automatique, c'est-à-dire que nous devons joindre la table à elle-même. Pour un représentant qui a un directeur, la jointure sur la condition de salesperson1.id = salesperson2.manager_id nous donnera des paires de représentants et leurs directeurs. Il n'est pas nécessaire d'utiliser LEFT JOIN ici, car nous ne recherchons que les commerciaux qui ont un responsable.

Vous pouvez ajouter la condition de filtrage (manager.salary < salesperson.salary) soit dans une clause WHERE, soit comme condition supplémentaire dans la clause JOIN (créant ainsi une jointure non équivoque). Une jointure non équivoque peut être une bonne démonstration de vos compétences en matière de JOIN, mais dans ce cas, le résultat est le même.

Si vous souhaitez vous entraîner davantage sur les JOIN pour votre prochain entretien, consultez notre cours Les jointures en SQL , qui vous guidera à travers tous les types de JOIN et la manière de les utiliser au mieux.

2) Produits plus chers

Tâche : Pour chaque produit, comptez le nombre de produits qui sont plus chers que lui.

Réponse :

SELECT
  p1.name,
  COUNT(p2.name) AS num_products
FROM product p1
LEFT JOIN product p2
  ON p1.price < p2.price
GROUP BY p1.name;

Explication :

Cette tâche peut être facilement résolue à l'aide d'une auto-jonction ou d'une sous-requête. Étant donné que les interviews d'Amazon ont tendance à s'appuyer fortement sur des JOINs compliqués (et que les requêtes JOINs ont tendance à s'exécuter plus rapidement que les sous-requêtes), nous utiliserons une autre jointure automatique.

Pour ignorer le filtrage après la jointure, nous plaçons la condition product1.price < product2.price dans la jointure elle-même, à l'intérieur de la clause ON. Un LEFT JOIN garantit que nous incluons le produit le plus cher. Le produit le plus cher n'a pas de produits correspondants à joindre, il serait donc exclu dans une jointure normale.

3) Produits supérieurs au salaire d'un représentant

Tâche : Pour chaque représentant, indiquez le nombre de produits qu'il vend et dont le prix est supérieur à son salaire. Incluez tous les vendeurs dans le résultat, même s'ils ne vendent pas de tels produits.

Réponse :

SELECT
  s.id,
  s.name,
  s.salary,
  s.manager_id,
  COUNT(p.name) AS expensive_products
FROM salesperson s
LEFT JOIN product p
  ON s.id = p.salesperson_id
  AND p.price > s.salary
GROUP BY
  s.id,
  s.name,
  s.salary,
  s.manager_id;

Explication :

Rejoindre product et salesperson. Pour ne compter que les produits dont le prix est supérieur au salaire du représentant, utilisez la condition de filtrage p.price > s.salary. Vous pourriez vouloir la placer dans la clause WHERE après JOIN, mais la plupart des représentants n'ont pas de produits aussi chers et la condition de filtrage les éliminera tous. Au lieu de cela, placez cette condition en tant que condition supplémentaire lorsque vous joignez les deux tables et utilisez une clause LEFT JOIN.

Si un vendeur n'a pas de produits dont le coût est supérieur à son salaire, il sera tout de même conservé dans le résultat final grâce à LEFT JOIN. Son compte expensive_products aura une valeur de 0.

Si vous souhaitez en savoir plus sur l'utilisation des JOIN non équivoques, consultez ces exemples détaillés de JOIN non équivoques.

4. prix des produits par catégorie

Tâche : Pour chaque produit, comparez son prix au prix moyen des autres produits de la catégorie. Indiquez le nom et le prix du produit, ainsi que le prix moyen de tous les autres produits de la même catégorie.

Réponse :

SELECT
  p1.name,
  p1.price,
  AVG(p2.price) AS avg_price_in_category
FROM product p1
JOIN product p2
  ON p1.category = p2.category
  AND p1.id != p2.id
GROUP BY
  p1.name,
  p1.price;

Explication :

Pour calculer le prix moyen de tous les autres produits de la même catégorie, vous devez créer des paires comme celle-ci : produit - autre produit de la même catégorie. Pour ce faire, vous devez joindre la table des produits à la catégorie du produit. Cela inclura les lignes où le produit est joint à lui-même, de sorte qu'une condition supplémentaire doit être ajoutée dans le champ JOIN: product1.name != product2.name. (Cette condition peut également figurer dans la clause WHERE).

Maintenant que toutes les paires de produits sont complètes, nous pouvons calculer la moyenne du prix du deuxième produit de la paire et regrouper par le premier produit. Nous obtiendrons ainsi le résultat souhaité.

5. le salaire moyen d'un vendeur

Tâche : Montrez toutes les données du représentant ainsi qu'une colonne avec le salaire moyen de tous les représentants et la différence entre le site salary de ce représentant et le site average_salary de tous les représentants.

Réponse :

SELECT 
  id,
  name,
  salary,
  manager_id,
  AVG(salary) OVER() AS average_salary,
  salary - AVG(salary) OVER() AS salary_difference
FROM salesperson;

Explication :

Pour obtenir le salaire moyen de tous les vendeurs, nous utiliserons la fonction de fenêtre AVG(). Elle fonctionne de la même manière que la fonction agrégée AVG(), mais pour en faire une fonction de fenêtre, nous utilisons OVER() au lieu de GROUP BY.

Utilisez une clause OVER() vide pour obtenir la valeur moyenne du salaire de tous les vendeurs. Pour plus de clarté, nommez la colonne average_salary. Soustrayez le salaire de chaque commercial (qui est différent pour chaque ligne) de average_salary (qui est le même pour chaque ligne) pour obtenir la différence de salaire pour chaque commercial. Nommez ce résultat salary_difference.

Si vous souhaitez vous entraîner davantage sur les fonctions de fenêtre, consultez notre cours Fonctions de fenêtrage . Il propose plus de 200 exercices interactifs qui vous permettront de connaître les fonctions de fenêtre en détail !

6. prix relatifs des produits

Tâche : Pour chaque produit, indiquez le rapport entre son prix et le prix du produit le plus cher de cette catégorie.

Réponse :

SELECT
  name,
  price,
  price / MAX(price) OVER(PARTITION BY category) AS price_ratio
FROM product;

Explication :

Pour calculer le rapport entre le prix du produit actuel et le prix du produit le plus cher de la même catégorie, nous devons utiliser la formule suivante : ratio = price / (max price from the same category).

Nous ne pouvons pas utiliser GROUP BY ici, car il réduit les lignes individuelles ; à la place, nous utilisons la fonction de fenêtre MAX(). Contrairement à l'exercice précédent, nous allons ici spécifier explicitement la partition. Nous voulons obtenir un résultat distinct pour chaque catégorie, la syntaxe que nous utiliserons sera donc : PARTITION BY category. Placez-la dans la clause OVER(), qui vient juste après la fonction de fenêtre MAX().

7. classement des prix des produits au sein des catégories

Tâche : Montrez comment le prix de chaque produit se classe dans sa catégorie. Affichez le nom, le prix et la catégorie de chaque produit ainsi que le rang du prix. Le produit le plus cher de sa catégorie doit être classé en premier et le produit le moins cher en dernier.

Réponse :

SELECT
  name,
  price,
  category,
  ROW_NUMBER() OVER(PARTITION BY category ORDER BY price DESC) AS category_rank
FROM product

Explication :

Pour créer un classement pour chaque ligne dans sa catégorie, nous devons utiliser les fonctions de la fenêtre de classement. Il existe plusieurs options : RANK() DENSE_RANK() ROW_NUMBER() Bien qu'elles puissent donner des résultats différents dans certains cas, dans le cas présent, n'importe laquelle d'entre elles fonctionnera. Pour en savoir plus sur les différences entre ces trois fonctions, consultez notre aperçu des fonctions de classement SQL.

Pour cet exemple, nous utiliserons ROW_NUMBER(). Cette fonction renvoie le numéro de chaque ligne dans sa catégorie, sans sauter ni répéter les numéros lorsque des produits partagent le même rang.

Nous utilisons ROW_NUMBER() et OVER() pour spécifier la taille de la fenêtre et l'ordre dans lequel les lignes sont classées. PARTITION BY category définit la fenêtre pour tous les produits de la même catégorie que la ligne actuelle. Cela garantit que seuls les produits de la même catégorie sont pris en compte pour le calcul du classement. Ensuite, ORDER BY price DESC définit l'ordre de classement (les produits dont les prix sont les plus élevés seront mieux classés).

8. les représentants les mieux payés des managers

Tâche : Parmi les représentants commerciaux qui dépendent d'un directeur, indiquez ceux qui sont les mieux payés. En d'autres termes : pour chaque directeur ayant des subordonnés, indiquez leur subordonné le mieux payé, mais pas le directeur lui-même.

Réponse :

WITH salesperson_with_highest_salary as (
  SELECT
    id,
    name,
    salary,
    manager_id,
    MAX(salary) OVER (PARTITION BY manager_id) AS highest_salary
  FROM salesperson
)
SELECT *
FROM salesperson_with_highest_salary
WHERE salary = highest_salary;

Explication :

Tout d'abord, nous allons créer un résultat intermédiaire : pour chaque vendeur, nous allons trouver son ID, son nom, son salaire, l'ID de son manager et le salaire le plus élevé parmi ses collègues (c'est-à-dire les représentants qui partagent le même manager). Nous utilisons la fonction de fenêtre MAX() avec OVER (PARTITION BY manager_id) pour trouver le salaire le plus élevé par groupe de représentants ayant le même manager_id.

Notez que nous enveloppons la requête dans une expression de table commune (CTE). Une CTE commence par le mot-clé WITH et un nom. Il donne ce nom aux résultats intermédiaires, qui peuvent être référencés ultérieurement dans la requête principale.

Ici, la CTE s'appelle salesperson_with_highest_salary. Si vous avez besoin d'un rappel sur le fonctionnement des CTE, consultez notre guide des expressions de table courantes en SQL. Pour une approche plus approfondie, essayez notre cours sur les expressions de table communes.

Nous n'avons besoin d'afficher que les commerciaux les mieux payés de leur groupe. Maintenant que vous disposez de l'adresse salary du représentant et de l'adresse highest_salary du groupe, vous pouvez filtrer les vendeurs dans la requête externe pour n'afficher que ceux qui ont le même salaire que le salaire le plus élevé du groupe. Si deux représentants ou plus d'un groupe ont le salaire le plus élevé, ils apparaîtront tous dans le résultat final.

9. produits les plus chers par catégorie

Tâche : Dans chaque catégorie, trouvez les trois produits les plus chers. Si plusieurs produits ont le même prix, incluez-les tous.

Réponse :

WITH product_ranks AS (
  SELECT
  name,
  price,
  category,
  DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank
  FROM Product
)
SELECT * 
FROM product_ranks
WHERE rank <= 3;

Explication :

Nous utilisons à nouveau une fonction window à l'intérieur d'un CTE. Cette fois, nous devons créer un classement supplémentaire pour chaque ligne au sein de sa catégorie. Pour ce faire, nous devons utiliser DENSE_RANK(), qui renverra tous les produits ayant les trois prix les plus élevés, même si certains produits ont le même prix.

Utilisez PARTITION BY category pour obtenir un classement distinct dans chaque catégorie et ORDER BY price DESC pour vous assurer que les produits les plus chers sont mieux classés. Enveloppez la requête qui trouve le classement pour chaque ligne dans un CTE afin que le classement soit disponible lors du filtrage des lignes. Toute ligne ayant un rang de 1, 2 ou 3 fait partie des trois premiers produits de sa catégorie ; utilisez ceci comme condition de filtrage dans la requête externe pour obtenir le résultat final.

Prêt pour votre entretien Amazon SQL ?

Bien joué ! Si vous avez réussi à résoudre toutes les tâches de notre préparation à l'entretien Amazon SQL, vous pouvez être confiant dans vos compétences SQL avancées. Bonne chance pour votre prochain entretien !

Vous avez envie de vous entraîner à des questions d'entretien SQL plus complexes ? Jetez un coup d'œil à ces 15 questions d'entretien difficiles qui combinent des sujets SQL théoriques et pratiques. Et si vous êtes à la recherche d'un plus grand nombre d'exercices pratiques, notre SQL avancé Practice Track en contient plus de 350. Bon entraînement !