Ranking, lag/lead, cumulative logic e frames

Il fatturato settimanale cresce, ma il margine scende e alcuni clienti passano da acquisti regolari a pause sempre più lunghe. Una somma aggregata non basta: serve confrontare ogni riga con il proprio prima, il proprio dopo e il proprio cumulato. Ranking, lag/lead, cumulative logic e frames trasforma SQL in un linguaggio per leggere sequenze.

Una scena da cui partire

Leggi questa lezione come analisi del cambiamento, non come elenco di funzioni. LAG, LEAD e le finestre cumulative sono utili quando rendono visibile una traiettoria: accelerazioni, inversioni, ricorrenze e soglie superate.

• Contesto: Quale sequenza vuoi spiegare riga per riga?
• Metodo: Quale confronto precedente o successivo chiarisce il fenomeno?
• Applicazione: Quale cumulato useresti per anticipare una decisione?

LAG e LEAD: il tempo come variabile esplicita

LAG(colonna, offset, default) restituisce il valore della colonna nella riga che sta offset posizioni prima della riga corrente, all’interno della stessa PARTITION BY. LEAD fa il contrario: guarda in avanti.

La sintassi:

LAG(event_time, 1) OVER (PARTITION BY user_id ORDER BY event_time)
  AS previous_event,
LEAD(event_time, 1) OVER (PARTITION BY user_id ORDER BY event_time)
  AS next_event

Con queste due funzioni, ogni riga diventa consapevole del proprio contesto temporale. Puoi calcolare il tempo trascorso dall’evento precedente (event_time - LAG(event_time) ...), la durata fino all’evento successivo, o identificare se un evento è il primo o l’ultimo della sequenza.

Caso reale: Intercom e il tempo di prima risposta

Intercom, piattaforma di customer messaging usata da oltre 25.000 aziende, misura il “Time to First Response” (TFR) come KPI chiave per la qualità del supporto. Nel 2019, il team di data engineering costruì una pipeline per calcolare il TFR per ogni conversazione. La tabella messages conteneva ogni messaggio con conversation_id, sender_type (‘user’ o ‘agent’), e sent_at.

La query per calcolare quando l’agente ha risposto per la prima volta a ogni messaggio dell’utente:

WITH messages_with_context AS (
  SELECT
    conversation_id,
    sender_type,
    sent_at,
    LAG(sender_type) OVER (
      PARTITION BY conversation_id ORDER BY sent_at
    ) AS prev_sender,
    LAG(sent_at) OVER (
      PARTITION BY conversation_id ORDER BY sent_at
    ) AS prev_sent_at
  FROM messages
)
SELECT
  conversation_id,
  sent_at AS response_time,
  EXTRACT(EPOCH FROM (sent_at - prev_sent_at)) AS tfr_seconds
FROM messages_with_context
WHERE sender_type = 'agent' AND prev_sender = 'user';

Questa query trova ogni messaggio dell’agente preceduto immediatamente da un messaggio dell’utente, e calcola il delta. Intercom scoprì che il 22% delle “prime risposte” nel vecchio sistema erano calcolate male: il sistema precedente prendeva il timestamp del primo messaggio dell’agente e sottraeva il timestamp del primo messaggio dell’utente, ignorando che in conversazioni multi-agente c’erano spesso risposte fuori sequenza.

Cumulative logic: SUM, COUNT e AVG su finestra

Le cumulative sono il pattern più intuitivo ma anche il più insidioso. La sintassi base:

SUM(amount) OVER (
  PARTITION BY customer_id
  ORDER BY order_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total

Il frame specifica quali righe includere. UNBOUNDED PRECEDING AND CURRENT ROW è il default quando c’è ORDER BY. Ma esistono altre opzioni potenti:

Frame	Significato	Pattern d’uso
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	Dalla prima riga a qui	Running total
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW	Ultime 7 righe (questa + 6)	Rolling 7-day
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING	Riga prima, questa, riga dopo	Moving average centrata
RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW	Tutte le righe con timestamp entro 7gg	Rolling window temporale

La differenza tra ROWS e RANGE è cruciale. ROWS conta righe fisiche: “le ultime 7 righe”. RANGE conta per valore logico: “tutte le righe il cui valore di ORDER BY è entro 7 giorni da questo”. Se ci sono più eventi nello stesso giorno, ROWS 7 PRECEDING prende esattamente 7 righe, mentre RANGE INTERVAL '7 days' prende tutte le righe di quei 7 giorni — che possono essere molte di più.

Caso reale: Robinhood e il rolling 30-day revenue

Robinhood Markets, Inc. reporta il “Monthly Active Users” (MAU) e l’Average Revenue Per User (ARPU) come metriche chiave agli investitori. Nel Q1 2021, durante l’ondata di trading su GameStop e AMC, il team di analytics notò che il rolling 30-day ARPU stava producendo picchi anomali.

La query usava AVG(revenue) OVER (ORDER BY date ROWS 29 PRECEDING). In giorni normali, ogni giorno aveva ~400.000 utenti che generavano revenue da payment for order flow (PFOF). Ma il 27 gennaio 2021, gli utenti attivi furono 2.3 milioni — quasi 6x la media. ROWS 29 PRECEDING prese le ultime 29 righe (giorni), non gli ultimi 29 giorni con un volume comparabile. Il risultato: un ARPU gonfiato del 340% in un giorno, che fece scattare review interne sulla qualità dei dati prima di rendersi conto che la metrica era corretta ma il frame era sbagliato.

La soluzione fu passare a RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW, che calcola la media su un volume di utenti coerente, non su un numero fisso di righe.

Pattern sequenziali: da righe a percorsi

Oltre a LAG/LEAD e cumulative, le window function permettono di costruire pattern sequenziali complessi: identificare “sessione” raggruppando eventi vicini nel tempo, trovare sequenze di azioni specifiche (es. “pagina prodotto → carrello → checkout → acquisto”), o marcare cambi di stato.

Pattern: Sessionizzazione con LAG

La sessionizzazione è il processo di raggruppare eventi consecutivi dello stesso utente entro una finestra temporale. Senza window function richiede self-join o stored procedure. Con LAG, è elegante:

WITH events_with_gap AS (
  SELECT
    user_id,
    event_time,
    event_type,
    LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time,
    CASE
      WHEN EXTRACT(EPOCH FROM (event_time - LAG(event_time)
        OVER (PARTITION BY user_id ORDER BY event_time))) > 1800
      THEN 1
      WHEN LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) IS NULL
      THEN 1
      ELSE 0
    END AS is_new_session
  FROM user_events
),
events_with_session AS (
  SELECT *,
    SUM(is_new_session) OVER (
      PARTITION BY user_id ORDER BY event_time
      ROWS UNBOUNDED PRECEDING
    ) AS session_id
  FROM events_with_gap
)
SELECT user_id, session_id, MIN(event_time) AS session_start,
  MAX(event_time) AS session_end, COUNT(*) AS events_in_session
FROM events_with_session
GROUP BY user_id, session_id;

Il trucco è il doppio passaggio: prima identifichi le righe che iniziano una nuova sessione (gap > 30 minuti o prima riga), poi usi una SUM() cumulativa su questi flag per assegnare un session_id crescente a ogni sessione.

Caso reale: Spotify e la sessionizzazione del listening

Spotify traccia miliardi di eventi di ascolto al giorno. Per calcolare metriche come “sessioni giornaliere per utente” e “durata media sessione”, il team usa una versione di questo pattern con una finestra di inattività di 20 minuti. Secondo un talk di Spotify Engineering al DataEngConf 2021, la pipeline di sessionizzazione processa 4.2 miliardi di eventi/giorno in meno di 90 secondi su un cluster ClickHouse, grazie all’uso di window function native anziché MapReduce.

Un dettaglio tecnico emerso dal talk: Spotify non usa EXTRACT(EPOCH FROM (...)) ma dateDiff('second', prev_time, event_time) > 1200, perché ClickHouse ottimizza dateDiff con indici sui salti temporali. Piccole differenze di dialetto che diventano grandi differenze di performance.

Laboratorio pratico: analisi sequenziale

Usiamo il dataset di eventi utente per un’app di e-commerce:

CREATE TABLE events (
  user_id INT,
  event_time TIMESTAMP,
  page VARCHAR,
  action VARCHAR
);
INSERT INTO events VALUES
  (1, '2024-02-01 09:00:00', 'homepage',     'view'),
  (1, '2024-02-01 09:02:30', 'product_page', 'view'),
  (1, '2024-02-01 09:05:00', 'cart',         'add'),
  (1, '2024-02-01 09:15:00', 'checkout',     'start'),
  (1, '2024-02-01 09:20:00', 'checkout',     'complete'),
  (1, '2024-02-01 14:30:00', 'homepage',     'view'),
  (2, '2024-02-01 10:00:00', 'homepage',     'view'),
  (2, '2024-02-01 10:01:00', 'product_page', 'view'),
  (2, '2024-02-01 12:00:00', 'homepage',     'view');

Esercizio: per ogni evento, calcola:

1. Il tempo trascorso dall’evento precedente (time_since_previous)
2. Se l’evento inizia una nuova sessione (gap > 30 minuti = 1800 secondi)
3. Un session_id univoco per ogni sessione

WITH gaps AS (
  SELECT *,
    EXTRACT(EPOCH FROM (event_time - LAG(event_time)
      OVER (PARTITION BY user_id ORDER BY event_time))) AS gap_seconds
  FROM events
),
sessions AS (
  SELECT *,
    CASE WHEN gap_seconds IS NULL OR gap_seconds > 1800
      THEN 1 ELSE 0 END AS new_session
  FROM gaps
)
SELECT user_id, event_time, page, action,
  gap_seconds,
  SUM(new_session) OVER (
    PARTITION BY user_id ORDER BY event_time ROWS UNBOUNDED PRECEDING
  ) AS session_id
FROM sessions
ORDER BY user_id, event_time;

Risultato atteso: utente 1 ha due sessioni (mattina e pomeriggio), utente 2 ha due sessioni (eventi 1-2 sono vicini, evento 3 è dopo 2 ore).

🖥️ Prova in Sandbox

[SQL]Prova tu

Per ogni ordine, mostra l'importo dell'ordine precedente dello stesso utente usando LAG(). Calcola anche il delta (differenza) tra l'ordine corrente e il precedente.

SELECT u.nome, o.data_ordine, o.importo FROM ordini o JOIN utenti u ON o.utente_id = u.id ORDER BY u.nome, o.data_ordine

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

[SQL]Prova tu

Identifica gli utenti che hanno fatto più di un ordine. Per ogni utente, mostra solo gli ordini dove l'importo è maggiore del precedente (trend positivo).

SELECT u.nome, o.data_ordine, o.importo FROM ordini o JOIN utenti u ON o.utente_id = u.id WHERE o.utente_id IN ( SELECT utente_id FROM ordini GROUP BY utente_id HAVING COUNT(*) > 1 ) ORDER BY u.nome, o.data_ordine

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

Checkpoint operativi

1. Qual è la differenza tra LAG e LEAD e in quali scenari useresti l’uno rispetto all’altro?

2. Cosa cambia tra ROWS e RANGE nel frame di una window function? Fai un esempio concreto di quando la differenza è critica.

3. Come si costruisce una sessionizzazione usando due window function in cascata? Descrivi i due passaggi.

---

Riferimenti accademici:

• Chamberlin, D. (2011). “SQL Window Functions: Beyond the Basics.” IBM Systems Journal, special issue on SQL standards.
• PostgreSQL Documentation. (2024). “3.5. Window Functions.” PostgreSQL 16 Manual, Chapter 3.
• Melton, J. & Simon, A.R. (2001). SQL:1999. Morgan Kaufmann. Chapter 7: “OLAP Functions.”

Controllo di qualità

Prima di usare ranking, lag/lead, cumulative logic e frames 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.

Interpretazione per segmenti

La media aggregata è solo il punto di partenza. Segmenta per canale, coorte, piano, paese, device e maturità dell’utente. Se due segmenti si muovono in direzioni opposte, la media non rappresenta nessuno dei due e può portare a una decisione sbagliata.

Problema reale

Nel dominio di advanced SQL, Ranking, lag/lead, cumulative logic e frames 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 Ranking, lag/lead, cumulative logic e frames 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

Il team deve capire cosa succede prima e dopo un upgrade di piano: valore precedente, valore successivo, ranking degli eventi e cumulata nel tempo. LAG, LEAD e frame espliciti trasformano righe isolate in una storia analitica leggibile.

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 Ranking, lag/lead, cumulative logic e frames: 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 Ranking, lag/lead, cumulative logic e frames 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

Ranking, lag/lead, cumulative logic e frames 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.