SQL Avanzato per Colloqui Data Analyst: Subquery, Pivot e Ottimizzazione delle Query nel 2026

I processi di selezione per le posizioni di data analyst hanno subito una trasformazione profonda negli ultimi anni. Nel 2026, le valutazioni tecniche SQL non si limitano piu a semplici query SELECT e join elementari. Le aziende del settore tecnologico — dalle scale-up alle grandi imprese, passando per le societa di consulenza sui dati — richiedono ai candidati una padronanza consolidata delle subquery correlate, delle trasformazioni pivot e dell'ottimizzazione delle query. Queste competenze rappresentano il principale fattore di differenziazione tra un profilo junior e un analista di dati con esperienza consolidata. Questo articolo approfondisce i pattern SQL avanzati che compaiono regolarmente nelle valutazioni tecniche, con esempi concreti eseguibili su PostgreSQL 17.

Subquery Correlate e Subquery Classiche a Confronto

La distinzione tra subquery correlata e subquery classica costituisce un concetto fondamentale valutato in quasi tutti i colloqui tecnici SQL. Una subquery classica (non correlata) viene eseguita una sola volta e produce un risultato indipendente dal contesto della query esterna. Il motore del database calcola il risultato della subquery e lo utilizza come valore fisso per filtrare le righe della query principale.

Nell'esempio seguente, la subquery calcola la media globale degli importi degli ordini. Questo valore viene determinato una sola volta, dopodiche ogni riga della tabella orders viene confrontata con questa soglia unica.

-- regular_subquery_threshold.sql
-- Find all orders above the average order value
SELECT
  order_id,
  customer_id,
  total_amount
FROM orders
WHERE total_amount > (
  -- Executes once, returns a single scalar value
  SELECT AVG(total_amount)
  FROM orders
);

Una subquery correlata, al contrario, fa riferimento a una o piu colonne della query esterna. Il motore deve quindi rivalutare la subquery per ciascuna riga dell'insieme di risultati esterno. Questo comportamento ha implicazioni dirette sulle prestazioni, specialmente su tabelle di grandi dimensioni.

La domanda classica dei colloqui che illustra questo meccanismo consiste nell'identificare i dipendenti il cui stipendio supera la media salariale del proprio reparto.

-- correlated_subquery_department.sql
-- Employees earning above their department average
SELECT
  e.employee_id,
  e.name,
  e.department,
  e.salary
FROM employees e
WHERE e.salary > (
  -- Re-evaluated for each employee's department
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department = e.department
);

Questa query ricalcola la media salariale per il reparto di ciascun dipendente esaminato. Su una tabella con diverse centinaia di migliaia di righe, il costo in termini di tempo di esecuzione diventa proibitivo. L'approccio ottimizzato consiste nel precalcolare le medie dipartimentali in un CTE (Common Table Expression) e poi effettuare un semplice join con la tabella principale.

-- optimized_with_cte.sql
-- Same result, single pass over the data
WITH dept_avg AS (
  SELECT
    department,
    AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT
  e.employee_id,
  e.name,
  e.department,
  e.salary
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_salary;

La versione con CTE scorre la tabella employees una sola volta per calcolare le medie, poi effettua il join. Il guadagno in termini di prestazioni risulta spesso notevole sui volumi di produzione. Durante i colloqui, la capacita di scrivere la versione correlata e poi refactorizzarla spontaneamente in CTE rappresenta un indicatore significativo di maturita tecnica.

Pattern di Subquery Ricorrenti nei Colloqui Data Analyst

Tre pattern di subquery si presentano sistematicamente nelle valutazioni tecniche per le posizioni di data analyst: EXISTS per le verifiche di appartenenza, le subquery scalari nelle clausole SELECT e le tabelle derivate nelle clausole FROM.

EXISTS vs. IN — La clausola EXISTS interrompe la valutazione non appena viene trovata una corrispondenza. Sui dataset di grandi dimensioni, questa strategia di cortocircuito offre un vantaggio prestazionale significativo rispetto a IN, che materializza l'intero risultato della subquery prima di procedere al confronto.

-- exists_vs_in.sql
-- Customers who placed at least one order in 2026 (EXISTS — preferred)
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.order_date >= '2026-01-01'
);

-- Equivalent with IN (slower on large datasets)
SELECT customer_id, name
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  WHERE order_date >= '2026-01-01'
);

Gli ottimizzatori moderni di PostgreSQL possono in alcuni casi riscrivere un IN come semi-join, riducendo il divario prestazionale. Tuttavia, EXISTS resta la formulazione consigliata perche esprime piu chiaramente l'intenzione dello sviluppatore e gestisce correttamente i casi che coinvolgono valori NULL.

Subquery scalare in SELECT — Questo pattern consente di aggiungere una colonna calcolata all'insieme di risultati senza richiedere un JOIN esplicito. La subquery restituisce un singolo valore per ciascuna riga della query principale.

-- scalar_subquery_select.sql
-- Each product with its category's total revenue
SELECT
  p.product_id,
  p.product_name,
  p.category,
  (
    SELECT SUM(oi.quantity * oi.unit_price)
    FROM order_items oi
    JOIN products p2 ON oi.product_id = p2.product_id
    WHERE p2.category = p.category
  ) AS category_total_revenue
FROM products p;

Questo pattern si rivela appropriato per arricchire un insieme di risultati con una o due colonne aggregate. Oltre tale soglia, un CTE combinato con un JOIN produce un codice piu leggibile e generalmente piu performante, poiche il calcolo di aggregazione viene effettuato una sola volta per categoria.

Query Pivot con Aggregazione Condizionale

Le query pivot costituiscono un argomento imprescindibile dei colloqui per data analyst. Il principio consiste nel trasformare dati memorizzati riga per riga in un formato colonnare — tipicamente, convertire mesi registrati come valori in una colonna in colonne distinte come jan_revenue, feb_revenue. Sebbene alcuni DBMS offrano un operatore PIVOT nativo, l'approccio portabile e universalmente atteso nei colloqui si basa su espressioni CASE all'interno di funzioni di aggregazione.

Il primo esempio illustra un pivot mensile del fatturato per prodotto. Ciascuna espressione CASE isola le righe corrispondenti a un mese specifico, mentre la funzione SUM aggrega gli importi.

-- pivot_monthly_revenue.sql
-- Monthly revenue pivot: one row per product, one column per month
SELECT
  product_id,
  product_name,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 1
    THEN quantity * unit_price ELSE 0 END) AS jan_revenue,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 2
    THEN quantity * unit_price ELSE 0 END) AS feb_revenue,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 3
    THEN quantity * unit_price ELSE 0 END) AS mar_revenue,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 4
    THEN quantity * unit_price ELSE 0 END) AS apr_revenue,
  -- Repeat for remaining months
  SUM(quantity * unit_price) AS total_revenue
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2026-01-01'
GROUP BY product_id, product_name
ORDER BY total_revenue DESC;

La stessa tecnica di aggregazione condizionale si applica ad analisi multidimensionali. L'esempio seguente segmenta l'attivita degli utenti per tipo di dispositivo e calcola la percentuale di sessioni da mobile — un indicatore frequentemente richiesto nei colloqui orientati al prodotto.

-- pivot_user_activity.sql
-- User activity: sessions by day of week and device type
SELECT
  user_id,
  COUNT(CASE WHEN device_type = 'mobile' THEN 1 END) AS mobile_sessions,
  COUNT(CASE WHEN device_type = 'desktop' THEN 1 END) AS desktop_sessions,
  COUNT(CASE WHEN device_type = 'tablet' THEN 1 END) AS tablet_sessions,
  ROUND(
    COUNT(CASE WHEN device_type = 'mobile' THEN 1 END) * 100.0 / COUNT(*),
    1
  ) AS mobile_pct
FROM user_sessions
WHERE session_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY user_id
HAVING COUNT(*) >= 5  -- Only users with meaningful activity
ORDER BY mobile_pct DESC;

La clausola HAVING filtra gli utenti con un numero di sessioni insufficiente per risultare statisticamente significativo. Questo tipo di filtraggio post-aggregazione viene regolarmente affrontato nei colloqui per verificare la comprensione dell'ordine di esecuzione delle clausole SQL.

Pivot Dinamico con CROSSTAB in PostgreSQL

Quando le categorie del pivot non sono note in anticipo, l'estensione tablefunc di PostgreSQL mette a disposizione la funzione CROSSTAB. Questo approccio risulta particolarmente utile per i report le cui dimensioni variano dinamicamente — ad esempio, un pivot trimestrale i cui trimestri futuri non sono ancora presenti nei dati.

-- crosstab_dynamic_pivot.sql
-- Enable the extension (once per database)
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- Revenue by product and quarter using CROSSTAB
SELECT *
FROM crosstab(
  $$
    SELECT
      product_name,
      'Q' || EXTRACT(QUARTER FROM order_date)::TEXT AS quarter,
      SUM(quantity * unit_price) AS revenue
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE EXTRACT(YEAR FROM o.order_date) = 2026
    GROUP BY product_name, quarter
    ORDER BY product_name, quarter
  $$,
  $$ VALUES ('Q1'), ('Q2'), ('Q3'), ('Q4') $$
) AS pivot_table(
  product_name TEXT,
  q1_revenue NUMERIC,
  q2_revenue NUMERIC,
  q3_revenue NUMERIC,
  q4_revenue NUMERIC
);

La prima subquery racchiusa tra dollar-quote produce i dati sorgente nel formato (categoria_riga, categoria_colonna, valore). La seconda subquery definisce esplicitamente le categorie attese come colonne. Questa separazione consente al pianificatore di gestire i valori mancanti (un prodotto senza vendite nel Q3, ad esempio) inserendo automaticamente dei NULL.

Ottimizzazione delle Query SQL: Piani EXPLAIN e Diagnostica delle Prestazioni

La capacita di diagnosticare una query lenta distingue gli analisti di dati operativi dai profili puramente teorici. Il comando EXPLAIN ANALYZE costituisce lo strumento principale per comprendere come il pianificatore PostgreSQL esegue una query e individuare i colli di bottiglia.

-- explain_analyze_example.sql
-- Analyze a slow query to identify bottlenecks
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
  c.customer_id,
  c.name,
  COUNT(o.order_id) AS order_count,
  SUM(o.total_amount) AS lifetime_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2025-01-01'
GROUP BY c.customer_id, c.name
HAVING SUM(o.total_amount) > 1000
ORDER BY lifetime_value DESC;

Diversi indicatori nell'output di EXPLAIN meritano particolare attenzione. Un Seq Scan (scansione sequenziale) su una tabella di grandi dimensioni segnala generalmente l'assenza di un indice adeguato. Il confronto tra le righe stimate (rows) e quelle effettivamente elaborate (actual rows) rivela la precisione delle statistiche del pianificatore: uno scarto significativo indica statistiche obsolete, correggibili tramite un ANALYZE sulla tabella interessata. Le informazioni BUFFERS distinguono le pagine lette dalla cache (shared hit) da quelle lette dal disco (shared read), fornendo un indicatore diretto dell'efficienza della cache di PostgreSQL.

Il tipo di join selezionato (Nested Loop, Hash Join o Merge Join) dipende dalla dimensione dei dataset e dalla presenza di indici. Un Hash Join viene generalmente scelto per le grandi join senza indici, mentre un Nested Loop con Index Scan risulta ottimale per le join in cui una tabella e significativamente piu piccola dell'altra.

Strategie di Indicizzazione per l'Ottimizzazione SQL

L'indicizzazione rappresenta la leva di ottimizzazione piu diretta per migliorare i tempi di risposta delle query. I colloqui tecnici valutano la conoscenza dei diversi tipi di indice e la capacita di scegliere la strategia appropriata in funzione dei pattern delle query.

Un indice semplice su una colonna frequentemente utilizzata nelle clausole WHERE migliora i filtri su quella colonna. Un indice composito copre piu colonne, e l'ordine di dichiarazione delle colonne determina quali query possono trarne vantaggio: la regola del prefisso sinistro stabilisce che l'indice e utilizzabile solo se la query filtra sulle prime colonne dichiarate.

-- indexing_strategies.sql
-- Index on order_date: filters a small percentage of rows
CREATE INDEX idx_orders_date ON orders (order_date);

-- Composite index for queries filtering on both columns
CREATE INDEX idx_orders_customer_date
  ON orders (customer_id, order_date);

-- Covering index: includes columns needed in SELECT
-- Enables Index Only Scan (no table access)
CREATE INDEX idx_orders_covering
  ON orders (customer_id, order_date)
  INCLUDE (total_amount, status);

L'indice coprente (covering index) con la clausola INCLUDE aggiunge colonne supplementari alle foglie dell'albero B-tree senza integrarle nelle chiavi di ricerca. Questa tecnica consente un Index Only Scan — la query viene soddisfatta interamente dall'indice senza accesso alla tabella sottostante, eliminando un'intera categoria di operazioni di input/output.

Gli indici parziali offrono un'ottimizzazione aggiuntiva coprendo solo un sottoinsieme delle righe della tabella. Su tabelle in cui la maggior parte dei dati e archiviata o inattiva, un indice parziale sulle righe attive produce un indice considerevolmente piu piccolo e piu rapido da percorrere.

-- partial_index.sql
-- Only index active orders (smaller, faster index)
CREATE INDEX idx_orders_active
  ON orders (customer_id, order_date)
  WHERE status = 'active';

-- Only index recent data for dashboard queries
CREATE INDEX idx_orders_recent
  ON orders (order_date)
  WHERE order_date >= '2026-01-01';

Il pianificatore PostgreSQL utilizza un indice parziale solo quando la clausola WHERE della query corrisponde alla condizione dell'indice. Questa specificita viene regolarmente testata nei colloqui per verificare la comprensione approfondita del funzionamento degli indici.

Anti-pattern delle Query Ricorrenti e Relative Correzioni

Alcuni pattern di scrittura SQL, sebbene sintatticamente validi, neutralizzano le ottimizzazioni del motore di database. Saper riconoscerli e correggerli fa parte delle competenze valutate nei colloqui tecnici avanzati.

L'applicazione di una funzione su una colonna indicizzata costituisce l'anti-pattern piu diffuso. Quando una funzione trasforma il valore di una colonna prima del confronto, il pianificatore non puo piu utilizzare l'indice esistente su quella colonna, poiche i valori memorizzati nell'indice non corrispondono piu ai valori confrontati.

-- anti_patterns.sql
-- BAD: function on indexed column disables the index
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2026;

-- GOOD: range comparison uses the index
SELECT * FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date < '2027-01-01';

La riscrittura in confronto per intervallo preserva l'utilizzo dell'indice producendo un risultato funzionalmente identico. Questo refactoring rappresenta un guadagno prestazionale spesso considerevole sulle tabelle di grandi dimensioni.

Il secondo anti-pattern riguarda l'utilizzo di NOT IN con subquery suscettibili di contenere valori NULL. Questa trappola risulta particolarmente insidiosa perche la query viene eseguita senza errori ma restituisce un insieme di risultati vuoto. Nella logica ternaria SQL, qualsiasi confronto con NULL produce UNKNOWN, e NOT IN si comporta come una congiunzione di condizioni NOT EQUAL — se una di esse risulta UNKNOWN, l'intera condizione lo diventa.

-- not_in_null_trap.sql
-- BAD: returns empty if any customer_id is NULL in subquery
SELECT * FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id FROM orders
);

-- GOOD: NOT EXISTS handles NULLs correctly
SELECT * FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
);

NOT EXISTS gestisce correttamente i valori NULL poiche la condizione verte sull'esistenza di una corrispondenza, e non su un confronto di valori. Questo pattern risulta sistematicamente preferibile a NOT IN per le esclusioni basate su subquery.

Conclusione

La preparazione ai colloqui SQL per le posizioni di data analyst nel 2026 si basa sulla padronanza di diversi ambiti complementari. I punti essenziali da ricordare:

• Le subquery correlate vengono rieseguite per ciascuna riga dell'insieme di risultati esterno — refactorizzarle in CTE o in funzioni finestra migliora significativamente le prestazioni sulle tabelle di grandi dimensioni
• EXISTS supera IN per le verifiche di appartenenza, in particolare quando la subquery restituisce un gran numero di righe o contiene valori NULL
• L'aggregazione condizionale con CASE costituisce la tecnica pivot universale e portabile — padroneggiarla rappresenta un prerequisito prima di affrontare le sintassi PIVOT specifiche di ciascun DBMS
• EXPLAIN ANALYZE rivela il piano di esecuzione reale della query; l'attenzione deve concentrarsi sui Seq Scan, sulla precisione delle stime delle righe e sulla strategia di join selezionata
• L'ordine delle colonne negli indici compositi deve corrispondere ai pattern di filtraggio delle query — la regola del prefisso sinistro si applica sistematicamente
• Gli indici parziali e coprenti riducono le operazioni di input/output per pattern di query specifici senza il sovraccarico di un indice che copre l'intera tabella
• Le funzioni applicate su colonne indicizzate, NOT IN con valori NULL e SELECT * nelle query di produzione rappresentano anti-pattern da eliminare sistematicamente