LeetCode SQL problème et solution : Les trois premiers salaires d'un département

Vous cherchez une explication détaillée de l'exercice le plus difficile du LeetCode SQL 50 ? Nous avons compilé une explication détaillée de ce problème SQL, avec le processus de réflexion derrière la solution et les instructions sur la façon de construire la requête étape par étape.

LeetCode est une plateforme en ligne populaire qui propose une variété de problèmes SQL conçus pour vous aider à apprendre, à vous entraîner et à vous préparer aux entretiens. Elle propose également des problèmes de programmation dans d'autres langages. Les problèmes sont regroupés par thèmes tels que les algorithmes, les structures de données, les bases de données, la conception de systèmes et les différents langages de programmation.

LeetCode inclut également un plan d'étude SQL de 50 problèmes pour vous permettre d'acquérir toutes les compétences nécessaires pour réussir un entretien en science des données. Mais si vous cherchez plus que 50 problèmes pour apprendre et vous entraîner, pensez aux 120+ exercices de notre coursSQL pour les débutants . Nous avons également une collection de 100+ questions d'entretien SQL spécialement conçues pour que vous puissiez vous entraîner avant un entretien d'embauche.

Dans cet article, nous allons examiner de près le problème le plus difficile de l'ensemble SQL de LeetCode : Les trois premiers salaires d'un département. Nous discuterons du processus de réflexion derrière la solution et de la façon de construire la requête étape par étape. Prêt ?

Le problème des trois premiers salaires d'un département

Les trois premiers salaires du département est le seul problème difficile du plan d'étude SQL 50 de LeetCode. Il y a plusieurs endroits où vous pourriez être bloqué, alors attaquons ce problème étape par étape.

La tâche

Les dirigeants d'une entreprise souhaitent savoir qui gagne le plus d'argent dans chacun des départements de l'entreprise. Un employé qui gagne beaucoup d'argent est un employé dont le salaire se situe dans les trois premiers salaires uniques de ce département.

Rédigez une solution pour trouver les employés qui gagnent le plus d'argent dans chacun des départements.

Consultez le schéma de la base de données pour cette tâche :

La solution

Étape 1 : Trouver les salariés à hauts revenus de l'entreprise

Commençons par trouver les salariés à hauts revenus sans nous préoccuper des départements. La description du problème précise qu'un salarié à haut revenu est une personne dont le salaire est l'un des trois premiers salaires uniques. Cela signifie que s'il y a plusieurs personnes avec le même salaire, elles seront toutes des hauts salariés. Jetez un coup d'œil au tableau de résultats suivant :

Remarquez que nous ne sélectionnons que les quatre meilleurs salaires, mais qu'il peut y avoir plus de quatre hauts salaires. Par exemple, Will et Max partagent le même salaire et se retrouvent tous deux sur la liste des hauts salaires avec le même rang de salaire de 3.

Nous ne pouvons pas utiliser ORDER BY et LIMIT pour obtenir les hauts salaires, car nous ne pouvons pas savoir combien il y a de hauts salaires avant d'exécuter la requête ! Mais nous pouvons filtrer le résultat en fonction du rang du salaire. Voyons comment nous pouvons obtenir ce classement, puisqu'il n'est pas inclus dans la base de données.

Il existe trois fonctions de classement en SQL: RANK(), DENSE_RANK(), et ROW_NUMBER(). Toutes renvoient un classement des valeurs dans le cadre de la fenêtre spécifiée et dans le sens de l'ordre. La différence entre ces fonctions réside dans la manière dont elles traitent les valeurs dupliquées. Vous pouvez lire une explication détaillée sur l'utilisation des fonctions de classement dans notre Aperçu des fonctions de classement en SQL.

Examinez le même scénario que précédemment, mais maintenant avec toutes les fonctions de classement incluses :

Comme vous pouvez le voir, RANK() et DENSE_RANK() renvoient le même rang si les salaires sont identiques ; c'est le comportement dont nous avons besoin pour nous assurer que plusieurs personnes peuvent être incluses dans les hauts salaires si elles partagent le même salaire. Max serait exclu de la liste des 3 premiers si l'on utilisait ROW_NUMBER(), même s'il a un salaire parmi les 3 premiers.

La différence entre RANK() et DENSE_RANK() est la manière dont ils gèrent les trous dans le classement laissés par les valeurs dupliquées. RANK() saute un numéro de rang séquentiel pour chaque rang dupliqué, ce qui fait que les valeurs non dupliquées ont le même rang. DENSE_RANK() ne saute pas de rang en cas de duplication. C'est ce que nous voulons pour ce problème, car cela permet d'éviter d'omettre un salaire du top 3 lorsqu'il y a deux salaires du top 2 ou plus.

Voici une requête qui attribue un rang à tous les employés en fonction de leur salaire à l'aide de DENSE_RANK():

SELECT
  e.name,
  e.salary,
  DENSE_RANK() OVER(ORDER BY e.salary DESC)
FROM employee e;

Génial ! Nous obtenons maintenant le même résultat que dans le premier exemple : Tous les employés se voient attribuer un rang correspondant à leur salaire. L'étape suivante consiste à créer un classement distinct pour chaque département.

Étape 2 : Trouver les départements qui gagnent le plus d'argent

Nous pouvons ajouter un PARTITION BY à l'intérieur de la clause OVER() pour spécifier le cadre de la fenêtre pour chaque ligne en fonction d'une valeur donnée.

Dans ce problème, nous cherchons à créer un classement des trois premiers salaires dans chaque département, nous devons donc diviser le cadre de la fenêtre par l'identifiant du département. Pour en savoir plus sur cette expression, lisez notre article Comment utiliser PARTITION BY avec OVER().

Voici la requête qui attribuera les rangs séparément pour chaque département :

SELECT
  d.name AS department, 
  e.name AS employee,
  e.salary,
  DENSE_RANK() OVER(PARTITION BY d.id ORDER BY e.salary DESC) AS rank
FROM employee e
JOIN department d 
  ON d.id = e.departmentId;

Nous devons joindre les champs department et employee car la tâche nous demande d'afficher les noms des départements. Cependant, la clause PARTITION BY doit utiliser la colonne department.id (la clé primaire de la table). department ) pour s'assurer que deux départements ne correspondent pas à la même partition (ce qui pourrait se produire si deux départements ont le même nom).

Étape 3 : Construction de la requête finale

C'est génial ! Nous avons un classement local des salaires pour chaque département. Que se passe-t-il maintenant ?

Nous ne pouvons pas filtrer par la colonne rang car les fonctions fenêtres ne sont pas autorisées dans la clause WHERE.

Nous allons devoir envelopper la requête de classement dans un CTE et obtenir le résultat final à partir de là. Nous allons filtrer par la colonne rang pour n'inclure que les 3 premiers salaires de chaque département. N'oubliez pas de ne pas inclure le rang lui-même dans le résultat final, car nous n'avons besoin que des salaires.

Jetez un coup d'œil à la solution complète du problème :

WITH ranks AS (
SELECT
  d.name AS department, 
  e.name AS employee,
  e.salary,
  DENSE_RANK() OVER(PARTITION BY d.id ORDER BY e.salary DESC) AS salary_rank
FROM employee e
JOIN department d 
  ON d.id = e.departmentId
)
SELECT
  department,
  employee,
  salary,
FROM ranks 
WHERE  salary_rank <= 3;

Nous disposons ainsi d'une liste complète des personnes les mieux rémunérées pour chaque département !

Résolu : Le problème SQL le plus difficile de LeetCode !

Bien joué ! Nous avons fait une plongée profonde dans le problème le plus difficile du plan d'étude LeetCode SQL 50.

Vous cherchez plus de pratique au-delà du plan SQL 50 ? Essayez notre pisteLa pratique du SQL et choisissez parmi plus de 1 000 exercices SQL interactifs !