LeetCode SQL Problema e soluzione: Top tre stipendi del dipartimento

Cercate una spiegazione dettagliata dell'esercizio più difficile del LeetCode SQL 50? Abbiamo raccolto una spiegazione dettagliata di questo problema SQL, con il processo di pensiero alla base della soluzione e le istruzioni su come costruire la query passo dopo passo.

LeetCode è una popolare piattaforma online che offre una serie di problemi SQL progettati per aiutarvi a imparare, esercitarvi e prepararvi ai colloqui. Offre anche problemi di programmazione in altri linguaggi. I problemi sono raggruppati in argomenti come algoritmi, strutture dati, database, progettazione di sistemi e singoli linguaggi di programmazione.

LeetCode include anche un piano di studio di 50 problemi su SQL per acquisire tutte le competenze necessarie per superare un colloquio di lavoro nel campo della scienza dei dati. Ma se cercate qualcosa di più di 50 problemi su cui imparare ed esercitarvi, considerate gli oltre 120 esercizi del nostro corsoSQL per principianti . Abbiamo anche una raccolta di oltre 100 domande per il colloquio SQL, studiate appositamente per farvi esercitare prima di un colloquio di lavoro.

In questo articolo, esamineremo da vicino il problema più difficile del set SQL di LeetCode: Top Three Salaries del Dipartimento. Discuteremo il processo di pensiero alla base della soluzione e come costruire la query passo dopo passo. Pronti?

Il problema dei tre stipendi più alti del dipartimento

La Top Three degli stipendi del dipartimento è l'unico problema difficile del piano di studio SQL 50 di LeetCode. Ci sono diversi punti in cui ci si potrebbe bloccare, quindi affrontiamo questo problema un passo alla volta.

Il compito

I dirigenti di un'azienda sono interessati a vedere chi guadagna di più in ogni reparto dell'azienda. Un dipendente che guadagna molto è un dipendente che ha uno stipendio tra i primi tre stipendi unici per quel reparto.

Scrivete una soluzione per trovare i dipendenti che guadagnano di più in ogni reparto.

Date un'occhiata allo schema del database per questo compito:

La soluzione

Fase 1: trovare i dipendenti che guadagnano di più nell'azienda

Cominciamo a trovare i dipendenti che guadagnano molto, senza preoccuparci dei reparti. La descrizione del problema specifica che un dipendente di alto livello è chiunque abbia uno stipendio che rientra tra i primi 3 stipendi unici. Ciò significa che se ci sono più persone con lo stesso stipendio, saranno tutte persone che guadagnano molto. Osservate la seguente tabella dei risultati:

Si noti che, pur selezionando solo i primi quattro stipendi, possono esserci più di quattro persone che guadagnano molto. Ad esempio, Will e Max condividono lo stesso stipendio e finiscono entrambi nell'elenco degli stipendi più alti con lo stesso grado di stipendio, pari a 3.

Non è possibile utilizzare i metodi di base ORDER BY e LIMIT per trovare gli stipendi più alti, perché non è possibile sapere quanti sono gli stipendi più alti prima di eseguire la query! Possiamo però filtrare il risultato in base al livello di retribuzione. Vediamo come ottenere questa classifica, dato che non è inclusa nel database.

In SQL esistono tre funzioni di classificazione: RANK(), DENSE_RANK(), e ROW_NUMBER(). Tutte restituiscono una classifica di valori all'interno della finestra e della direzione di ordinamento specificate. La differenza tra loro è il modo in cui gestiscono i valori duplicati. Per una spiegazione dettagliata dell'uso delle funzioni di classificazione, consultare la nostra Panoramica delle funzioni di classificazione in SQL.

Guardate lo stesso scenario di prima, ma ora con tutte le funzioni di classificazione incluse:

Come si può vedere, RANK() e DENSE_RANK() restituiscono lo stesso grado se gli stipendi sono uguali; questo è il comportamento di cui abbiamo bisogno per assicurarci che più persone possano essere incluse tra quelle che guadagnano di più se condividono lo stesso stipendio. Max verrebbe escluso dall'elenco dei primi 3 se si utilizza ROW_NUMBER(), anche se ha uno stipendio da primo della classe.

La differenza tra RANK() e DENSE_RANK() è il modo in cui gestiscono i buchi nella classifica lasciati dai valori duplicati. RANK() salta un numero di rango sequenziale per ogni rango duplicato, facendo sì che i valori non duplicati abbiano lo stesso rango. DENSE_RANK() non salta i ranghi in caso di duplicati. Questo è ciò che vogliamo per questo problema, in quanto elimina l'omissione di uno stipendio top 3 quando ci sono due o più stipendi top 2.

Ecco una query che assegnerà a tutti i dipendenti un rango in base al loro stipendio utilizzando DENSE_RANK():

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

Ottimo! Ora otteniamo lo stesso risultato del primo esempio: A tutti i dipendenti vengono assegnati i gradi corrispondenti al loro stipendio. Il passo successivo consiste nel creare una classifica separata per ogni reparto.

Passo 2: trovare i dipendenti che guadagnano di più nei reparti

È possibile aggiungere un PARTITION BY all'interno della clausola OVER() per specificare la cornice della finestra per ogni riga in base a un determinato valore.

In questo problema, vogliamo creare una classifica dei primi tre stipendi di ciascun reparto, quindi dobbiamo suddividere la finestra in base all'ID del reparto. Per un approfondimento su questa espressione, leggere il nostro articolo su Come usare PARTITION BY con OVER().

Ecco la query che assegnerà i gradi separatamente per ogni reparto:

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;

Dobbiamo unire i campi department e employee perché il task ci chiede di visualizzare i nomi dei reparti. Tuttavia, la clausola PARTITION BY dovrebbe utilizzare la colonna department.id (la chiave primaria della tabella) per fare in modo che il nome del reparto venga visualizzato. department chiave primaria della tabella) per assicurarsi che non ci siano due dipartimenti mappati alla stessa partizione (cosa che potrebbe accadere se due dipartimenti hanno lo stesso nome).

Passo 3: creazione della query finale

Ottimo! Abbiamo una classifica locale degli stipendi per ogni reparto. E ora?

Non possiamo filtrare in base alla colonna rank perché le funzioni windows non sono ammesse nella clausola WHERE.

Dovremo avvolgere la query di classificazione in una CTE e ottenere il risultato finale da lì. Filtreremo in base alla colonna rank per includere solo i primi 3 stipendi di ogni reparto. Ricordare di non includere il rango stesso nel risultato finale, perché ci servono solo gli stipendi.

Date un'occhiata alla soluzione completa del problema:

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;

In questo modo abbiamo un elenco completo di chi guadagna di più per ogni reparto!

Risolto: Il problema SQL più difficile di LeetCode!

Ben fatto! Abbiamo fatto un'immersione profonda nel problema più difficile del programma di studio LeetCode SQL 50.

Volete fare più pratica oltre al programma SQL 50? Provate la nostra tracciaPratica su SQL e scegliete tra oltre 1.000 esercizi interattivi di SQL!