Ranking, lag/lead, cumulative logic e frames

Il fatturato settimanale cresce, ma il margine scende e alcuni clienti rallentano gli acquisti. Una somma aggregata non basta a leggere una situazione del genere: serve confrontare ogni riga con la precedente, con la successiva e con il cumulato fino a quel punto. Ranking, lag/lead, cumulative logic e frames sono ciò che trasforma SQL in uno strumento per leggere sequenze e traiettorie, non solo totali.

Quando il totale nasconde la dinamica

Nell’analisi avanzata con SQL il problema è scrivere query corrette e significative anche quando grain, finestre temporali, coorti e casi limite si complicano. Non si tratta di imparare funzioni a memoria. Si tratta di migliorare decisioni concrete partendo da dati, assunzioni esplicite e controlli che rendano il risultato discutibile.

Una mappa per orientarsi

Ogni analisi tecnica regge solo se punta a una scelta. Questo schema tiene insieme i quattro punti che la rendono utile.

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

Definire l’unità di lavoro

Per rendere analizzabile il problema con ranking, lag/lead, cumulative logic e frames, definisci prima l’unità di lavoro, che sia riga, partizione, finestra o coorte. Poi scegli il segnale principale, tra correttezza, performance, presenza di duplicati e stabilità. Da lì fissi la baseline di confronto e la decisione attesa. Senza questo passaggio i risultati rischiano di essere inutili o, peggio, fuorvianti.

Elemento	Specifica richiesta
Unità di analisi	riga, partizione, finestra, join, coorte o metrica temporale
Segnale principale	correttezza, performance, duplicati, grain e stabilità
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

LAG e LEAD: il tempo come variabile esplicita

Le funzioni LAG e LEAD confrontano ogni riga con la precedente o la successiva all’interno di una partizione ordinata. Questo rende visibile la dinamica temporale: quanto tempo è passato, se un evento è il primo o l’ultimo, se c’è stato un cambiamento di stato.

Intercom, per esempio, è riuscita a calcolare correttamente il “Time to First Response” (TFR) solo dopo aver usato LAG per associare ogni messaggio dell’agente al messaggio utente precedente, evitando gli errori dovuti a risposte fuori sequenza.

Cumulative logic: somma, conteggio e media su finestre

Le funzioni cumulative calcolano running total, medie mobili e rolling window. La differenza tra ROWS e RANGE nel frame è il punto critico: ROWS conta righe fisiche, RANGE ragiona sui valori logici, per esempio intervalli temporali.

Robinhood ha scoperto che usare ROWS 29 PRECEDING per un rolling 30-day ARPU produceva picchi falsi, perché contava righe e non giorni con volume simile. Passando a RANGE con un intervallo temporale, la metrica è diventata stabile e rappresentativa.

Pattern sequenziali: sessionizzazione e percorsi

Combinando LAG e logica cumulativa si costruiscono pattern più complessi, come la sessionizzazione, che raggruppa eventi vicini nel tempo in sessioni distinte. Il processo ha due passaggi: identificare le righe che aprono una nuova sessione, quando il gap temporale supera una soglia, e assegnare un session_id cumulando quei flag.

Spotify usa questo pattern per processare miliardi di eventi al giorno e ottenere metriche di sessione accurate senza far esplodere i costi di calcolo.

Leggere una traiettoria con prudenza

Un team deve capire l’effetto di un upgrade di piano. Confronta il valore precedente con quello successivo, mette in ranking gli eventi, guarda la cumulata nel tempo. LAG, LEAD e frames trasformano dati isolati in una narrazione analitica leggibile.

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

Costruire l’analisi a tre profondità

Comincia con una scheda sintetica: decisione da supportare, metrica primaria, baseline, rischio e azione. Poi costruisci una tabella con tre segmenti o scenari e per ciascuno indica cosa cambia, le spiegazioni alternative e i controlli da fare prima di agire. Il passo più ambizioso è un decision memo con ipotesi, dati, criteri di esclusione, controlli di qualità, soglia decisionale, rischio residuo e piano di monitoraggio. Per il materiale vanno bene dati reali o sintetici, purché abbiano almeno 200 righe, una dimensione temporale, dei segmenti e una metrica di outcome.

L’errore che svuota l’analisi

L’errore più comune è usare ranking, lag/lead, cumulative logic e frames come etichetta senza un processo dietro: grafici senza decisione, metriche senza baseline, conclusioni senza assunzioni dichiarate. La domanda chiave è: se il risultato fosse instabile, quale scelta sbaglierei? Se non c’è una risposta concreta, manca il collegamento tra analisi e azione.

Verifica di comprensione

Per controllare la presa, prova a rispondere. Qual è la differenza tra LAG e LEAD e quando useresti l’uno o l’altro? Cosa cambia tra ROWS e RANGE in un frame, con un esempio in cui la scelta è critica? Come costruiresti una sessionizzazione con due window function, passo per passo? Quale decisione concreta dovrebbe migliorare questa lezione? E quale output consegneresti a uno stakeholder non tecnico?

Riepilogo operativo

Ranking, lag/lead, cumulative logic e frames sono strumenti potenti solo quando guidano decisioni più chiare. Servono a trasformare dati isolati in storie sequenziali, sempre dentro un modello che collega problema, misura, controllo e azione.

---

Laboratorio pratico: analisi sequenziale

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

user_id	event_time	page	action
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

L’obiettivo è calcolare, per ogni evento, il tempo trascorso dall’evento precedente, se l’evento apre una nuova sessione con un gap superiore a 30 minuti, e un session_id univoco per sessione. La soluzione usa LAG per il gap, un CASE per identificare le nuove sessioni e una SUM cumulativa per assegnare il session_id.

---

Controllo di qualità

Prima di applicare queste tecniche verifica completezza, duplicati, timezone, definizioni e segmenti esclusi. Molte analisi falliscono perché i dati di partenza non rappresentano il comportamento atteso, e nessuna window function corregge un grain sbagliato a monte.

Interpretazione per segmenti

La media aggregata è solo un punto di partenza. Segmenta per canale, coorte, piano, paese o device. Quando segmenti diversi si muovono in direzioni opposte, la media diventa fuorviante e ti porta verso decisioni sbagliate.