Domande di intervista su Amazon SQL

Vi state preparando per un colloquio in Amazon? Date un'occhiata a queste domande di colloquio e verificate le vostre conoscenze professionali di SQL!

La preparazione ai colloqui di lavoro con grandi aziende come Amazon può risultare impegnativa, ma è sempre possibile esercitarsi in anticipo e rafforzare le proprie competenze SQL! In questo articolo, esamineremo le domande del colloquio SQL di Amazon e vi aiuteremo a prepararvi per il vostro prossimo colloquio importante. Volete padroneggiare tutto l'SQL e superare qualsiasi compito? Il nostro percorso completo SQL dalla A alla Z, che copre ogni angolo di SQL con più di 850 esercizi, vi aiuterà a raggiungere il vostro obiettivo.

Se state cercando altre domande per colloqui di lavoro su SQL, date un'occhiata al nostro articolo Top 100+ SQL Interview Questions and Practice Exercises. In esso abbiamo raccolto molti altri esercizi SQL per aiutarvi a prepararvi al vostro prossimo colloquio.

Come funziona un colloquio Amazon SQL?

Potete candidarvi a una posizione in Amazon sul sito amazon.jobs e utilizzare la funzione di ricerca per trovare tutte le offerte di lavoro relative a SQL. Potete anche cercare parole chiave correlate come "database", "data science" o "analyst" per trovare posizioni simili.

Se la vostra candidatura viene accettata e disponete di un CV pertinente, un selezionatore vi contatterà per una rapida conversazione telefonica. Questa prima telefonata molto probabilmente non sarà di tipo tecnico, ma si concentrerà sul determinare se siete adatti alla cultura dell'azienda. Una volta superata questa selezione, inizia il colloquio tecnico.

Gli intervistatori tecnici di Amazon vi faranno domande relative a SQL e ai dati, oltre ad altre domande specifiche per la posizione per cui vi siete candidati. I colloqui SQL di Amazon sono noti per trattare dapprima le basi della sintassi e poi per addentrarsi in complesse JOIN e funzioni a finestra. Potete esercitarvi su questi argomenti con il nostro corso SQL JOIN e con il nostro corso completo sulle funzioni finestra .

In questo articolo ci concentreremo sulla pratica delle JOIN e delle funzioni finestra. Se volete rinfrescare le vostre conoscenze sulle basi di SQL, date un'occhiata al nostro elenco di domande comuni per i colloqui SQL.

Conoscere i dati

Per questo esercizio utilizzeremo due semplici tabelle, salesperson e product. Esaminiamo ciascuna di esse.

La tabella salesperson contiene informazioni su tutti i rappresentanti dell'azienda. Ognuno di loro è identificato con un id (memorizzato come dato INT), e ha un name (TESTO), un salary (NUMERICO) e un manager_id (INT) opzionale. Quando è presente, l'ID manager punta al id di un altro rappresentante che supervisiona il venditore corrente.

La tabella product memorizza le informazioni sui prodotti venduti dai venditori. Ogni prodotto è identificato dal suo id (INT) e ha un name (TEXT), price (NUMERIC), category (TEXT) e un salesperson_id (INT) obbligatorio. Quest'ultimo campo indica l'id del rappresentante che vende il prodotto.

Ora che abbiamo esaminato i dati, iniziamo gli esercizi. È consigliabile avere a disposizione i nostri fogli informativi gratuiti sul linguaggio SQL. I nostri SQL JOIN Cheat Sheet e Window Functions (Funzioni Finestra) Cheat Sheet vi aiuteranno se avete bisogno di un rapido promemoria sulla sintassi di un comando.

Domande, risposte e spiegazioni di Amazon SQL Interview

1. Rappresentanti di vendita ad alto guadagno

Compito: Trovare tutti i venditori il cui stipendio è superiore a quello dei loro manager.

Risposta:

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

Spiegazione:

Per associare ogni venditore al suo manager, è necessario utilizzare un self-join, ovvero unire la tabella con se stessa. Per un rappresentante che ha un manager, unendo la condizione di salesperson1.id = salesperson2.manager_id si ottengono le coppie di rappresentanti e manager. Non c'è bisogno di un LEFT JOIN, perché stiamo cercando solo i venditori che hanno un manager.

È possibile aggiungere la condizione di filtraggio (manager.salary < salesperson.salary) in una clausola WHERE o come condizione aggiuntiva nella clausola JOIN (creando una join non-equi). Una join non-equi può essere una buona dimostrazione delle vostre abilità di JOIN, ma in questo caso il risultato è lo stesso.

Se volete fare più pratica con le JOIN per il vostro prossimo colloquio, consultate il nostro corso SQL JOIN , che vi guiderà attraverso tutti i tipi di JOIN e come utilizzarli al meglio.

2. Prodotti più costosi

Compito: Per ogni prodotto, contare il numero di prodotti più costosi.

Risposta:

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;

Spiegazione:

Questo compito può essere facilmente risolto con una self-join o una subquery. Dal momento che le interviste di Amazon tendono a fare molto affidamento sulle JOIN complicate (e che le query JOIN tendono a essere più veloci delle subquery), useremo un'altra self-join.

Per saltare il filtraggio dopo la join, inseriamo la condizione product1.price < product2.price nella join stessa, all'interno della clausola ON. Un LEFT JOIN ci assicura di includere il prodotto più costoso. Il prodotto più costoso non ha prodotti corrispondenti con cui unirsi, quindi sarebbe escluso in una normale JOIN.

3. Prodotti che superano lo stipendio di un rappresentante

Compito: Per ogni rappresentante, mostrare quanti prodotti vende che hanno un prezzo superiore al suo stipendio. Includere tutti i venditori nel risultato, anche se non vendono tali prodotti.

Risposta:

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;

Spiegazione:

Unire product e salesperson. Per contare solo i prodotti che costano più dello stipendio del rappresentante, utilizzare la condizione di filtro p.price > s.salary. Si potrebbe inserirla nella clausola WHERE dopo la JOIN, ma la maggior parte dei rappresentanti non ha prodotti così costosi e la condizione di filtraggio li eliminerà tutti. Invece, inserire questa condizione come condizione aggiuntiva quando si uniscono le due tabelle e utilizzare una LEFT JOIN.

Se un venditore non ha prodotti che costano più del suo stipendio, sarà comunque mantenuto nel risultato finale dalla condizione LEFT JOIN. Il suo conteggio expensive_products avrà un valore pari a 0.

Se volete saperne di più su quando usare le JOIN non equi, date un'occhiata a questi esempi dettagliati di JOIN non equi.

4. Prezzo del prodotto per categoria

Compito: Per ogni prodotto, trovare il suo prezzo rispetto al prezzo medio degli altri prodotti della categoria. Mostrare il nome e il prezzo del prodotto, insieme al prezzo medio di tutti gli altri prodotti della stessa categoria.

Risposta:

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;

Spiegazione:

Per calcolare il prezzo medio di tutti gli altri prodotti della stessa categoria, è necessario creare coppie come questa: prodotto - altro prodotto della stessa categoria. Per farlo, si deve unire la tabella dei prodotti alla categoria del prodotto. Questo includerà le righe in cui il prodotto è unito a se stesso, quindi è necessario aggiungere un'ulteriore condizione in JOIN: product1.name != product2.name. (Questa condizione potrebbe anche essere inserita nella clausola WHERE).

Ora che tutte le coppie di prodotti sono state completate, è possibile calcolare la media del prezzo del secondo prodotto della coppia e raggruppare per il primo prodotto. In questo modo otterremo il risultato desiderato.

5. Stipendio medio dei venditori

Compito: Mostrare tutti i dati dei rappresentanti e una colonna con lo stipendio medio di tutti i rappresentanti e la differenza tra salary e average_salary per tutti i rappresentanti.

Risposta:

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

Spiegazione:

Per ottenere il salario medio di tutti i venditori, si utilizzerà la funzione AVG(). Funziona come la funzione aggregata AVG(), ma per renderla una funzione finestra si usa OVER() invece di GROUP BY.

Utilizzare una clausola OVER() vuota per ottenere il valore medio del salario per tutti i venditori. Nominare la colonna average_salary per chiarezza. Sottrarre il salario di ogni rappresentante (che è diverso per ogni riga) da average_salary (che è lo stesso per ogni riga) per ottenere la differenza di salario per ogni rappresentante. Nominate questo risultato salary_difference.

Se desiderate fare più pratica con le funzioni di finestra, date un'occhiata al nostro corso Window Functions (Funzioni Finestra) . Offre oltre 200 esercizi interattivi che vi permetteranno di conoscere le funzioni delle finestre in modo approfondito!

6. Prezzi relativi dei prodotti

Compito: Per ogni prodotto, indicare il rapporto tra il suo prezzo e quello del prodotto più costoso della categoria.

Risposta:

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

Spiegazione:

Per calcolare il rapporto tra il prezzo del prodotto attuale e il prezzo del prodotto più costoso della stessa categoria, è necessario utilizzare questa formula: ratio = price / (max price from the same category).

Non è possibile utilizzare GROUP BY in questo caso, poiché collassa le singole righe; si utilizza invece la funzione finestra MAX(). A differenza dell'ultimo esercizio, qui specificheremo esplicitamente la partizione. Vogliamo ottenere un risultato separato per ogni categoria, quindi la sintassi che utilizzeremo sarà: PARTITION BY category. Inserirla nella clausola OVER(), subito dopo la funzione finestra MAX().

7. Classifica dei prezzi dei prodotti all'interno delle categorie

Compito: Mostrare la posizione del prezzo di ogni prodotto all'interno della sua categoria. Mostrate il nome, il prezzo e la categoria di ogni prodotto insieme alla classifica dei prezzi. Il prodotto più costoso della categoria deve essere al primo posto e quello più economico all'ultimo.

Risposta:

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

Spiegazione:

Per creare una classifica per ogni riga all'interno della sua categoria, è necessario utilizzare le funzioni della finestra di classificazione. Esistono alcune opzioni: RANK() DENSE_RANK() , o ROW_NUMBER(). Anche se in alcuni casi potrebbero dare risultati diversi, in questo caso ognuna di esse funzionerà. Per saperne di più sulle differenze tra queste tre funzioni, consultate la nostra panoramica sulle funzioni di ranking SQL.

Per questo esempio, utilizzeremo ROW_NUMBER(). Questa funzione restituirà il numero di ogni riga all'interno della sua categoria, senza saltare o ripetere i numeri quando i prodotti condividono lo stesso rango.

Utilizziamo ROW_NUMBER() insieme a OVER() per specificare la dimensione della finestra e l'ordine di classificazione delle righe. PARTITION BY categoria imposta la finestra su tutti i prodotti della stessa categoria della riga corrente. In questo modo si garantisce che solo i prodotti della stessa categoria vengano considerati per il calcolo della classifica. Quindi ORDER BY price DESC imposta l'ordine di classifica (i prodotti con prezzi maggiori saranno classificati più in alto).

8. Rappresentanti più pagati dai manager

Compito: Tra i rappresentanti di vendita che fanno capo a un manager, mostrare quelli che vengono pagati di più. In altre parole: per ogni manager che ha dei subordinati, mostrare il suo subordinato più pagato, ma non il manager stesso.

Risposta:

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;

Spiegazione:

Per prima cosa, creeremo un risultato intermedio: per ogni venditore, troveremo il suo ID, il nome, lo stipendio, l'ID del manager e lo stipendio più alto tra i suoi colleghi (cioè i rappresentanti che condividono lo stesso manager). Utilizziamo la funzione finestra MAX() insieme a OVER (PARTITION BY manager_id) per trovare il salario più alto per gruppo di rappresentanti con lo stesso manager_id.

Si noti che la query è racchiusa in un'espressione di tabella comune (CTE). Una CTE inizia con la parola chiave WITH e un nome. Questo nome viene assegnato ai risultati intermedi, che possono essere richiamati successivamente nella query principale.

In questo caso, la CTE si chiama salesperson_with_highest_salary. Se avete bisogno di un promemoria sul funzionamento delle CTE, consultate la nostra guida alle comuni espressioni di tabella in SQL. Per un approccio più approfondito, provate il nostro corso sulle espressioni di tabella comuni.

Dobbiamo mostrare solo i rappresentanti di vendita che sono i più pagati del loro gruppo. Ora che si dispone di salary e di highest_salary del gruppo, è possibile filtrare i venditori nella query esterna per mostrare solo quelli che hanno lo stesso stipendio di quello più alto del gruppo. Se due o più rappresentanti di un gruppo hanno lo stipendio più alto, tutti saranno mostrati nel risultato finale.

9. Prodotti più costosi per categoria

Compito: In ogni categoria, trovare i tre prodotti con il prezzo più alto. Se più prodotti hanno lo stesso prezzo, includerli tutti.

Risposta:

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;

Spiegazione:

Utilizziamo nuovamente una funzione finestra all'interno di una CTE. Questa volta dobbiamo creare una classifica aggiuntiva per ogni riga all'interno della sua categoria. A tale scopo è necessario utilizzare DENSE_RANK(), che restituirà tutti i prodotti con i tre prezzi più alti, anche se alcuni prodotti hanno lo stesso prezzo.

Utilizzare PARTITION BY category per ottenere una classifica separata all'interno di ogni categoria e ORDER BY price DESC per assicurarsi che i prodotti con il prezzo più alto ottengano una classifica più alta. Avvolgere la query che trova la classifica per ogni riga in una CTE, in modo che la classifica sia disponibile quando si filtrano le righe. Ogni riga con rango 1, 2 o 3 rientra nei primi tre prodotti della categoria; utilizzare questa condizione come filtro nella query esterna per ottenere il risultato finale.

Pronti per il colloquio con Amazon SQL?

Ben fatto! Se avete risolto con successo tutti i compiti della nostra preparazione al colloquio Amazon SQL, potete essere sicuri delle vostre competenze SQL avanzate. Buona fortuna per il vostro prossimo colloquio!

Siete alla ricerca di domande di colloquio SQL più complesse per esercitarvi? Date un'occhiata a queste 15 domande di colloquio difficili che combinano argomenti teorici e pratici di SQL. E se siete alla ricerca di una gamma più ampia di esercizi pratici, la nostra traccia pratica SQL avanzato ne contiene oltre 350. Buona pratica!