Execution order, logical plans e query thinking

Una query filtra una metrica calcolata, usa una window function nel posto sbagliato e produce numeri plausibili ma non riconciliabili. Il problema non è la sintassi: è una mappa mentale errata dell’ordine con cui SQL costruisce il risultato. Execution order, logical plans e query thinking serve a leggere la query come una sequenza di trasformazioni controllabili.

Una scena da cui partire

Leggi la lezione come debug di un piano logico. Ogni clausola deve avere un ruolo: prima definisci il set di righe, poi le aggregazioni, poi le finestre, poi il filtro finale e l’ordinamento. Quando salti questa disciplina, gli errori diventano difficili da vedere.

• Contesto: Quale passaggio crea davvero le righe analizzate?
• Metodo: Quale clausola va spostata per rendere corretta la logica?
• Applicazione: Come descriveresti il piano a chi deve revisionare la query?

Il problema reale: perché l’ordine di esecuzione uccide le query

La maggior parte degli analisti impara SQL nell’ordine sintattico: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Questo è l’ordine in cui si scrive una query, ma non l’ordine in cui il motore la esegue. Il vero ordine di esecuzione logico, standardizzato dallo SQL-92 e rimasto invariato in tutti i dialetti moderni (PostgreSQL, ClickHouse, BigQuery, Snowflake), è radicalmente diverso:

1. FROM (inclusi JOIN e prodotti cartesiani)
2. WHERE (filtro sulle righe sorgente)
3. GROUP BY (aggregazione)
4. HAVING (filtro sui gruppi)
5. WINDOW FUNCTIONS (calcolo su finestra)
6. SELECT (proiezione e alias)
7. DISTINCT (deduplicazione)
8. ORDER BY (ordinamento)
9. LIMIT/OFFSET (paginazione)

La conseguenza operativa è controintuitiva: le window function vengono calcolate dopo WHERE e GROUP BY ma prima di SELECT, DISTINCT e ORDER BY. Questo significa che ogni filtro in WHERE restringe il dataset su cui la finestra opera, mentre ogni alias definito in SELECT non è ancora disponibile quando la finestra viene calcolata. Il bug di Spotify era esattamente questo: il WHERE veniva logicamente prima della finestra, ma il team aveva ragionato come se la finestra “vedesse” tutte le righe e il filtro successivo le restringesse.

Lo stesso schema si ripete in decine di contesti aziendali. Un’analista di Zalando nel 2021 calcolò la retention a 30 giorni usando ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) e filtrando WHERE rn = 1 per prendere il primo ordine. Il problema: il WHERE sulle window function non è permesso direttamente — devi wrappare in una subquery o CTE. Usò una CTE, ma ordinò per order_date ASC dimenticando che gli ordini restituiti avevano data negativa, prendendo il reso come “primo ordine”. La retention risultò del 34% più alta del reale, e il team Product usò quei numeri per giustificare un investimento di 800.000 euro in un programma fedeltà che non serviva.

Il modello concettuale: il query plan come mappa decisionale

Il vero salto mentale non è memorizzare l’ordine di esecuzione, ma imparare a leggere il logical query plan come una mappa di decisioni. Ogni nodo del piano logico rappresenta un’operazione che trasforma un insieme di righe in un altro insieme di righe. La query non è una ricetta lineare: è una pipeline di trasformazioni relazionali.

Un modello robusto per ragionare sull’ordine di esecuzione si basa su tre domande, nell’ordine:

1. Qual è il grain di partenza? Definito dalla/e tabella/e nel FROM. Ogni riga rappresenta cosa? Un evento, una transazione, una sessione? Se non sai rispondere a questa domanda, ogni calcolo successivo è ambiguo.

2. Quali righe sopravvivono al WHERE? Il filtro riduce il grain. Ogni condizione nel WHERE elimina righe prima di qualsiasi aggregazione o finestra. Se il WHERE contiene date >= '2024-01-01', le finestre non vedranno mai righe del 2023 — nemmeno per calcolare running total o LAG.

3. Cosa trasforma ogni passaggio successivo? GROUP BY collassa righe in gruppi. Le window function calcolano metriche per ogni riga senza collassare. HAVING filtra gruppi. SELECT rinomina e proietta. Ogni passaggio ha una semantica precisa e un impatto misurabile sul risultato.

Questa struttura mentale è ciò che Jim Gray, Turing Award 1998 e padre dei database relazionali, chiamava “query literacy”: la capacità di leggere SQL non come istruzioni ma come specifiche dichiarative di trasformazioni su insiemi. In un suo saggio del 2004 per ACM SIGMOD, Gray notò che “la differenza tra un programmatore SQL e un analista SQL è che il primo scrive loop mentali, il secondo pensa in insiemi”. Le window function sono lo strumento che rende questo salto inevitabile: non puoi implementare un running total con un loop mentale senza perdere il controllo del comportamento della query.

Formalizzazione: l’ordine logico in notazione algebrica

Possiamo formalizzare il flusso di esecuzione usando l’algebra relazionale estesa. Data una query generica:

SELECT   A, B, SUM(C) OVER (PARTITION BY D ORDER BY E) AS running
FROM     T1 JOIN T2 ON T1.k = T2.k
WHERE    condition
GROUP BY A, B
HAVING   aggregate_condition
ORDER BY A

La sequenza logica è:

1. Join relazionale: R₁ = T1 ⋈ T2 (sulla condizione di join)
2. Filtro: R₂ = σ_condition(R₁)
3. Aggregazione: R₃ = γ_{A,B; agg_funcs}(R₂)
4. Filtro su gruppi: R₄ = σ_aggregate_condition(R₃)
5. Window function: R₅ = ω_{PARTITION BY D ORDER BY E; SUM(C)}(R₄)
6. Proiezione: R₆ = π_{A, B, running}(R₅)
7. Ordinamento: R₇ = τ_A(R₆)

La notazione algebrica rende esplicito ciò che la sintassi SQL nasconde: le window function sono un’operazione distinta (ω) che avviene dopo aggregazione e filtro, ma prima di proiezione e ordinamento. Il dataset su cui ω opera è già aggregato e filtrato — non è la tabella originale.

Questo ha implicazioni fondamentali per il debugging. Quando una query produce un risultato inaspettato, la strategia corretta non è “controllare la sintassi della window function”, ma risalire il piano logico un passo alla volta, verificando il dataset intermedio a ogni stadio. Parti dal FROM: il grain è corretto? Poi WHERE: le righe sopravvissute sono quelle giuste? Poi GROUP BY: i gruppi hanno la cardinalità attesa? Solo dopo queste verifiche guardi la window function.

David DeWitt, co-fondatore del database parallelo Gamma e professore emerito a Wisconsin-Madison, descrive questo approccio come “query bisection”: sezionare la query in punti di controllo intermedi per isolare l’errore. In un paper del 1995 su VLDB Journal, DeWitt dimostrò che l’80% degli errori in query analitiche complesse si trova nel grain o nei filtri, non nella logica delle funzioni finestra — ma la maggior parte degli analisti inizia il debug dalle window function, perdendo in media 3 volte più tempo.

Caso applicato: Shopify e la riconciliazione delle revenue

Nel Q3 2022, il team di data analytics di Shopify affrontò un problema di riconciliazione: il GMV (Gross Merchandise Volume) calcolato dal data warehouse differiva del 2.7% dal GMV riportato dal sistema di pagamento (Shopify Payments). Su un trimestre con 46.2 miliardi di dollari di GMV, il gap era di 1.25 miliardi — statisticamente significativo e finanziariamente rilevante per la comunicazione agli investitori.

Il team usò esattamente l’approccio a piani logici per isolare il problema. La query incriminata calcolava il GMV mensile per merchant con una finestra di riconciliazione che includeva chargeback e refund:

WITH merchant_gmv AS (
  SELECT
    merchant_id,
    DATE_TRUNC('month', order_date) AS month,
    SUM(gross_amount) AS gross,
    SUM(refund_amount) AS refunds,
    SUM(gross_amount) OVER (
      PARTITION BY merchant_id
      ORDER BY DATE_TRUNC('month', order_date)
    ) AS cumulative_gross
  FROM orders
  WHERE order_status NOT IN ('cancelled', 'test')
  GROUP BY merchant_id, DATE_TRUNC('month', order_date)
)
SELECT month, SUM(gross - refunds) AS net_gmv
FROM merchant_gmv
GROUP BY month;

Il piano logico rivelò due problemi:

1. WHERE applicato prima della finestra: order_status NOT IN ('cancelled', 'test') escludeva ordini che erano stati cancellati dopo essere stati inclusi nei calcoli del sistema di pagamento. Shopify Payments registra un ordine come GMV al momento dell’autorizzazione, ma lo storna solo al chargeback — mentre la query lo escludeva direttamente. Il grain era diverso tra i due sistemi.

2. La finestra PARTITION BY merchant_id operava su dati già aggregati: il SUM(gross_amount) OVER (...) stava sommando somme mensili già aggregate, non le transazioni individuali. Il cumulative era corretto solo se ogni merchant aveva esattamente una riga per mese — ma merchant con più valute o più storefront avevano righe multiple, gonfiando il cumulativo.

La soluzione fu riprogettare la query con il grain corretto: un livello intermedio di staging che calcolava il GMV al grain della transazione (non del merchant-mese) e una window function sulla tabella non aggregata. Il gap si ridusse allo 0.03%, dentro la tolleranza di riconciliazione.

Shopify documentò questo caso internamente come esempio di “logical plan debugging” e lo incluse nella formazione obbligatoria per ogni nuovo data analyst. Il principio operativo divenne: prima verifica il grain, poi verifica i filtri, solo dopo guarda le finestre.

Laboratorio pratico: debug di un piano logico

L’esercizio è strutturato su un dataset reale (reso anonimo) di un marketplace con 2.4 milioni di transazioni, 340.000 utenti e 18 mesi di storico. La query da debuggare calcola il revenue per utente con una window function, ma produce risultati palesemente errati. Il compito è applicare il metodo del piano logico per identificare e correggere l’errore.

Dataset

Tabella	Righe	Grain	Colonne chiave
transactions	2.4M	Una riga per transazione	txn_id, user_id, amount, txn_date, status
users	340K	Una riga per utente	user_id, signup_date, country, segment

Query da debuggare

SELECT
  user_id,
  amount,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY txn_date) AS cumulative_revenue,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY txn_date DESC) AS rn
FROM transactions
WHERE status = 'completed' AND country = 'IT'
ORDER BY user_id, txn_date;

Analisi guidata

1. Grain della query: La query parte da transactions (grain: txn_id). Il WHERE filtra per status e country. Ma country non è una colonna di transactions — è nella tabella users. La query fallirà con “column not found”. Primo errore: il grain non include le colonne necessarie per il filtro. Serve un JOIN con users.

2. Ordine di esecuzione dopo la correzione: Anche aggiungendo il JOIN, il WHERE filtra prima della finestra. La cumulative revenue includerà solo le transazioni completed e solo utenti italiani. Se l’obiettivo era la cumulative di tutte le transazioni (inclusi refund pending), il filtro va spostato in una CTE esterna.

3. La ROW_NUMBER in ordine DESC: rn = 1 darà l’ultima transazione di ogni utente. Se combinata con WHERE rn = 1, prenderà solo l’ultima transazione, non la più recente completata. Se l’ultima transazione è un refund, il dato è fuorviante.

🖥️ Prova in Sandbox

[SQL]Prova tu

Correggi la query di debug: aggiungi un JOIN con utenti per recuperare il paese. Usa il campione nel sandbox per verificare che il country sia accessibile.

SELECT o.id, o.utente_id, o.importo, o.data_ordine, o.categoria FROM ordini o WHERE o.categoria = 'Sport' AND o.importo > 100 ORDER BY o.data_ordine

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

[SQL]Prova tu

Scrivi una query che calcola il totale cumulativo degli importi per utente (running total), ordinato per data. Nota: la window function opera sul risultato del WHERE — solo ordini Sport >100€.

SELECT utente_id, data_ordine, importo FROM ordini ORDER BY utente_id, data_ordine

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

Checkpoint operativi

1. Perché l’ordine sintattico di SQL (SELECT-FROM-WHERE…) è diverso dall’ordine di esecuzione logico?

2. Qual è la posizione delle window function nell’ordine di esecuzione e perché è rilevante per i filtri WHERE?

3. Come si usa il piano logico per fare debugging di una query analitica complessa? Descrivi il metodo in 3 passaggi.

4. Nel caso Shopify, quali erano i due problemi di grain e come sono stati risolti?

---

Riferimenti accademici:

• Gray, J. (2004). “The Next Database Revolution.” ACM SIGMOD International Conference on Management of Data, pp. 1-4.
• DeWitt, D. & Gray, J. (1992). “Parallel Database Systems: The Future of High Performance Database Systems.” Communications of the ACM, 35(6), pp. 85-98.
• Melton, J. & Simon, A. R. (2001). SQL:1999 - Understanding Relational Language Components. Morgan Kaufmann. Capitolo 7: “Window Functions and Ordered Set Functions.”

Controllo di qualità

Prima di usare execution order, logical plans e query thinking in una decisione, controlla sempre completezza, duplicati, timezone, definizioni cambiate e segmenti esclusi. Molte analisi apparentemente sofisticate falliscono perché il dato di partenza misura un comportamento diverso da quello che il team crede di osservare.

Problema reale

Nel dominio di advanced SQL, Execution order, logical plans e query thinking serve a risolvere questo problema: scrivere query analitiche corrette anche quando grain, finestre, coorti e casi limite diventano complessi. La lezione non va trattata come teoria isolata, ma come un modo per migliorare una scelta concreta con dati, assunzioni esplicite e controlli minimi.

Obiettivo operativo: Comprendere il problema analitico e il contesto decisionale; Applicare esempi, metriche e controlli a casi reali. Se alla fine non sai indicare quale decisione cambia, quale dato osservi e quale errore vuoi evitare, la lezione non è ancora diventata competenza applicata.

Modello concettuale

Fase	Cosa chiarire	Output
Domanda	Quale scelta reale deve migliorare?	Decisione da prendere
Misura	Quale segnale osservabile rappresenta il problema?	Metrica o dato sorgente
Controllo	Quale baseline rende il risultato interpretabile?	Confronto credibile
Azione	Che cosa cambia dopo l’analisi?	Prossimo passo operativo

Il modello concettuale è intenzionalmente semplice: decisione, dato, controllo, azione. Ogni approfondimento tecnico deve rafforzare almeno uno di questi quattro punti.

Formalizzazione rigorosa

Per rendere Execution order, logical plans e query thinking analizzabile, definisci prima l’unità di lavoro: riga, partizione, finestra, join, coorte o metrica temporale. Poi collega questa unità a una metrica osservabile: correttezza, performance, duplicati, grain e stabilità del risultato. Infine dichiara la decisione attesa: query, modello intermedio, test SQL o pattern riusabile.

Elemento	Specifica richiesta
Unità di analisi	riga, partizione, finestra, join, coorte o metrica temporale
Segnale principale	correttezza, performance, duplicati, grain e stabilità del risultato
Baseline	Periodo precedente, gruppo comparabile, benchmark o scenario controfattuale
Decisione	query, modello intermedio, test SQL o pattern riusabile
Rischio	Scambiare un numero disponibile per una prova sufficiente

La formalizzazione e solida quando un altro analista può riprodurre la logica, criticare le assunzioni e ottenere la stessa decisione partendo dagli stessi dati.

Esempio o caso studio

Una query complessa sembra misteriosa finché non la leggi nell’ordine logico: FROM, join, filtri, gruppi, finestre, selezione e ordinamento. Capire l’execution order permette di prevedere duplicati, filtri inefficaci e risultati che sembrano corretti solo in superficie.

Evidenza osservata	Lettura prudente	Azione consigliata
Il numero migliora	Potrebbe essere effetto reale o variazione normale	Cercare confronto e segmento
Un segmento cambia più degli altri	La media aggregata nasconde una differenza	Separare coorti o casi d’uso
Il costo cresce insieme al risultato	L’impatto va letto sul margine	Stimare trade-off e sostenibilità

Lab / esercizio

Livello base

Scrivi una scheda di una pagina per Execution order, logical plans e query thinking: decisione da supportare, metrica primaria, baseline, rischio principale e azione se il segnale e confermato.

Livello intermedio

Costruisci una tabella con tre segmenti, periodi o scenari. Per ciascuno indica cosa cambia, quale spiegazione alternativa e plausibile e quale controllo useresti prima di raccomandare un azione.

Livello research-grade

Prepara un decision memo: ipotesi, dati richiesti, criteri di esclusione, controlli di qualità, soglia decisionale, rischio residuo e piano di monitoraggio dopo la decisione.

Dataset e materiali consigliati

Usa warehouse SQL, dati ordini, eventi, sessioni, coorti, revenue e tabelle calendario. Se non hai accesso a dati reali, crea un dataset sintetico con almeno 200 righe, una dimensione temporale, una dimensione segmento e una metrica di outcome.

Errore tipico da evitare

L’errore più comune e usare Execution order, logical plans e query thinking come etichetta invece che come processo. Succede quando il team mostra un grafico senza decisione, una metrica senza baseline, o una conclusione senza indicare quale assunzione potrebbe invalidarla.

La domanda di controllo è: se questo risultato fosse instabile, quale scelta sbaglierei? Se la risposta non è concreta, manca ancora il collegamento tra analisi e azione.

Quiz o checkpoint

1. Quale decisione concreta dovrebbe migliorare questa lezione?
2. Quale unità di analisi rende il problema misurabile?
3. Quale baseline useresti per evitare una lettura ingenua?
4. Quale errore tipico potrebbe cambiare la conclusione?
5. Quale output consegneresti a uno stakeholder non tecnico?

Riepilogo operativo

Execution order, logical plans e query thinking diventa utile quando produce una decisione più chiara, non quando aggiunge terminologia. Usa il framework problema, modello, formalizzazione, esempio, lab e checkpoint per trasformare la lezione in pratica verificabile. Categoria: Decisione. Difficoltà: advanced. Tempo stimato: 18 min.