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

Phase	What to clarify	Output
Question	Which real choice needs improvement?	Decision to make
Measure	Which observable signal represents the problem?	Metric or source data
Control	Which baseline makes the result interpretable?	Credible comparison
Action	What changes after the analysis?	Next operational step

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.

Element	Requested specification
Unit of analysis	row, partition, window, join, cohort, or temporal metric
Primary signal	correttezza, performance, duplicati, grain e stabilità
Baseline	Previous period, comparable group, benchmark, or counterfactual scenario
Decision	query, intermediate model, SQL test, or reusable pattern
Risk	Mistaking an available number for sufficient proof

LAG and LEAD: time as an explicit variable

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.

Observed evidence	Cautious interpretation	Recommended action
The number improves	Potrebbe essere reale o variazione normale	Cercare confronto e segmentazione
One segment changes more than others	La media aggregata nasconde differenze	Separate cohorts or use cases
Cost grows along with the result	Leggere l’impatto sul margine	Estimate trade-offs and sustainability

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?

Operational Summary

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.

---

Practical lab: sequential analysis

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.

---

Quality control

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.

Interpretation by segments

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.