SQL per Data Analyst: Funzioni Finestra, CTE e Query Avanzate

Le funzioni finestra SQL, le CTE (Common Table Expressions) e i pattern di query avanzate rappresentano il nucleo fondamentale dell'SQL analitico. Che si tratti di prepararsi a un colloquio come data analyst o di affrontare report complessi, queste tecniche trasformano subquery verbose e difficili da leggere in SQL pulito, performante e manutenibile.

Funzioni Finestra SQL e la Clausola OVER

Una funzione finestra applica un calcolo su una "finestra" definita di righe. La clausola OVER controlla quali righe rientrano nella finestra e come vengono ordinate. A differenza delle funzioni aggregate con GROUP BY, le funzioni finestra mantengono ogni singola riga nel set di risultati.

La sintassi generale segue uno schema coerente su tutti i principali database: PostgreSQL, MySQL 8+, BigQuery, SQL Server e Snowflake.

-- window_function_syntax.sql
SELECT
  employee_id,
  department,
  salary,
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_salary_rank,
  SUM(salary) OVER (
    PARTITION BY department
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_salary_total
FROM employees;

PARTITION BY suddivide i dati in gruppi (in modo simile a GROUP BY, ma senza comprimere le righe). ORDER BY definisce la sequenza all'interno di ciascuna partizione. La clausola frame opzionale (ROWS BETWEEN ...) restringe la finestra a un intervallo specifico di righe.

ROW_NUMBER, RANK e DENSE_RANK a Confronto

Queste tre funzioni di classificazione appaiono simili, ma si comportano in modo diverso quando esistono valori duplicati. Scegliere quella sbagliata rappresenta una fonte frequente di bug nelle query analitiche.

-- ranking_comparison.sql
SELECT
  product_name,
  category,
  revenue,
  ROW_NUMBER() OVER (ORDER BY revenue DESC) AS row_num,
  RANK()       OVER (ORDER BY revenue DESC) AS rank_val,
  DENSE_RANK() OVER (ORDER BY revenue DESC) AS dense_rank_val
FROM products;

| product_name | revenue | row_num | rank_val | dense_rank_val | |-------------|---------|---------|----------|----------------| | Widget A | 50000 | 1 | 1 | 1 | | Widget B | 50000 | 2 | 1 | 1 | | Widget C | 40000 | 3 | 3 | 2 | | Widget D | 30000 | 4 | 4 | 3 |

ROW_NUMBER assegna sempre un intero sequenziale univoco: anche in caso di parita, una riga riceve arbitrariamente il numero inferiore. RANK assegna lo stesso numero ai valori pari ma salta i numeri successivi (1, 1, 3). DENSE_RANK gestisce anch'essa le parita, ma non salta mai i numeri (1, 1, 2). Per le query top-N in cui i duplicati devono condividere la stessa posizione, DENSE_RANK rappresenta generalmente la scelta corretta.

LAG e LEAD per l'Analisi Periodo su Periodo

LAG e LEAD accedono ai dati delle righe precedenti o successive senza ricorrere a self-join. Queste funzioni sono essenziali per calcolare la crescita periodo su periodo, individuare tendenze e identificare variazioni significative nei dati.

-- period_over_period.sql
SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
  ROUND(
    (revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0
    / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0),
    2
  ) AS mom_growth_pct,
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue
FROM monthly_sales
ORDER BY month;

Il secondo argomento di LAG/LEAD specifica l'offset (il valore predefinito e 1). Un terzo argomento opzionale fornisce un valore di default quando non esiste una riga a quell'offset, risultando utile per evitare NULL nella prima o nell'ultima riga. NULLIF nel calcolo della crescita previene gli errori di divisione per zero.

NTILE e Segmentazione in Percentili

NTILE distribuisce le righe in un numero specificato di gruppi approssimativamente uguali. I data analyst la utilizzano per l'analisi dei quartili, lo scoring per decili e la segmentazione della clientela.

-- customer_segmentation.sql
SELECT
  customer_id,
  total_spend,
  NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_quartile,
  NTILE(10) OVER (ORDER BY total_spend DESC) AS spend_decile
FROM (
  SELECT
    customer_id,
    SUM(order_total) AS total_spend
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY customer_id
) customer_totals;

Il quartile 1 contiene i clienti con la spesa piu elevata, il quartile 4 quelli con la spesa piu bassa. Questo pattern si mappa direttamente sulla segmentazione RFM (Recency, Frequency, Monetary), ampiamente utilizzata nell'analisi di marketing.

Common Table Expressions: Sostituire le Subquery Nidificate

Le CTE (clausole WITH) scompongono query complesse in passaggi denominati e leggibili. Ogni CTE funge da set di risultati temporaneo con un nome che esiste solo per la durata della query. Oltre alla leggibilita, le CTE facilitano il debug: ogni passaggio puo essere testato in modo indipendente.

-- cte_sales_analysis.sql
WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    product_category,
    SUM(amount) AS revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY DATE_TRUNC('month', order_date), product_category
),
ranked_categories AS (
  SELECT
    month,
    product_category,
    revenue,
    unique_customers,
    RANK() OVER (
      PARTITION BY month
      ORDER BY revenue DESC
    ) AS category_rank
  FROM monthly_revenue
)
SELECT
  month,
  product_category,
  revenue,
  unique_customers,
  category_rank
FROM ranked_categories
WHERE category_rank <= 3
ORDER BY month, category_rank;

CTE Ricorsive per Dati Gerarchici

Le CTE ricorsive risolvono problemi che coinvolgono dati gerarchici o strutture a grafo: organigrammi aziendali, alberi di categorie, distinte base e query di ricerca percorsi.

-- recursive_org_chart.sql
WITH RECURSIVE org_hierarchy AS (
  SELECT
    employee_id,
    employee_name,
    manager_id,
    1 AS depth,
    employee_name AS management_chain
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT
    e.employee_id,
    e.employee_name,
    e.manager_id,
    oh.depth + 1,
    oh.management_chain || ' > ' || e.employee_name
  FROM employees e
  INNER JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT
  employee_id,
  employee_name,
  depth,
  management_chain
FROM org_hierarchy
ORDER BY management_chain;

Pattern Analitici Avanzati: Gaps, Islands e Totali Progressivi

Il pattern gaps-and-islands identifica sequenze consecutive nei dati: periodi di abbonamento attivo, giorni di login consecutivi o cicli di produzione ininterrotti. Si tratta di una tecnica fondamentale per l'analisi delle serie temporali.

-- consecutive_login_streaks.sql
WITH login_days AS (
  SELECT DISTINCT
    user_id,
    DATE(login_timestamp) AS login_date
  FROM user_sessions
),
streaks AS (
  SELECT
    user_id,
    login_date,
    login_date - (ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY login_date
    ))::int AS streak_group
  FROM login_days
)
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*) AS streak_length
FROM streaks
GROUP BY user_id, streak_group
HAVING COUNT(*) >= 3
ORDER BY streak_length DESC;

L'idea alla base di questo pattern e elegante nella sua semplicita: sottraendo ROW_NUMBER dalla data, le date consecutive producono lo stesso valore di gruppo. Qualsiasi interruzione nella sequenza genera un nuovo gruppo, permettendo di identificare e misurare ciascuna serie consecutiva.

Combinare Funzioni Finestra con CASE per Analisi Condizionali

Le funzioni finestra possono essere combinate con espressioni CASE per creare metriche analitiche sofisticate: medie mobili, totali cumulativi per trimestre e rilevamento automatico dei valori anomali.

-- conditional_analytics.sql
SELECT
  order_date,
  product_category,
  amount,
  AVG(amount) OVER (
    PARTITION BY product_category
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d,
  SUM(amount) OVER (
    PARTITION BY product_category, DATE_TRUNC('quarter', order_date)
    ORDER BY order_date
  ) AS qtd_cumulative,
  CASE
    WHEN amount > 2 * AVG(amount) OVER (PARTITION BY product_category)
    THEN 'OUTLIER'
    ELSE 'NORMAL'
  END AS outlier_flag
FROM daily_sales
ORDER BY product_category, order_date;

Questa query combina tre tecniche in una singola istruzione: la media mobile a 7 giorni per individuare tendenze, il totale cumulativo quarter-to-date per monitorare gli obiettivi e un flag per i valori anomali basato sul confronto con la media della categoria. Questo tipo di analisi multidimensionale rappresenta un requisito comune nei colloqui per posizioni di data analyst.

Ottimizzazione delle Prestazioni per Query Analitiche

Quando piu funzioni finestra condividono la stessa definizione di partizione e ordinamento, la clausola WINDOW permette di definirla una sola volta e riutilizzarla. Oltre a ridurre la duplicazione del codice, questo approccio puo aiutare l'ottimizzatore del database a riconoscere partizioni identiche e migliorare il piano di esecuzione.

-- named_window.sql
SELECT
  department,
  employee_id,
  salary,
  RANK() OVER dept_window AS dept_rank,
  SUM(salary) OVER dept_window AS dept_total,
  AVG(salary) OVER dept_window AS dept_avg
FROM employees
WINDOW dept_window AS (
  PARTITION BY department
  ORDER BY salary DESC
);

Alcuni accorgimenti aggiuntivi per le prestazioni: filtrare i dati nelle clausole WHERE prima che le funzioni finestra vengano valutate, creare indici sulle colonne utilizzate in PARTITION BY e ORDER BY, e considerare la materializzazione dei risultati intermedi quando la stessa finestra viene utilizzata in piu query successive.

Conclusione

• Le funzioni finestra (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE) operano su insiemi di righe senza comprimerle, a differenza degli aggregati con GROUP BY
• Le CTE scompongono query complesse in passaggi denominati e testabili, sostituendo le subquery profondamente nidificate
• Le CTE ricorsive gestiscono dati gerarchici come organigrammi e alberi di categorie, ma richiedono limiti di profondita e colonne di join indicizzate
• La tecnica gaps-and-islands (ROW_NUMBER + aritmetica sulle date) identifica sequenze consecutive nei dati di serie temporali
• Le finestre denominate (clausola WINDOW) riducono la duplicazione del codice e possono migliorare l'ottimizzazione del piano di query
• Filtrare i dati nelle clausole WHERE prima della valutazione delle funzioni finestra per mantenere le prestazioni su tabelle di grandi dimensioni
• Questi pattern compaiono frequentemente nei colloqui di data analytics e si applicano direttamente al reporting quotidiano, alla segmentazione e all'analisi delle tendenze