Coding Interview Prep · Lezione

Isole con cambi di data e stato

Raggruppare periodi consecutivi con lo stesso stato, una domanda comune sullo stato di un abbonamento

Lezione 4 di 413 passaggi

Isole con cambi di data e stato è una lezione Coding Interview Prep gratuita su CoddyKit. Questa è la lezione 4 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento Coding Interview Prep, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso Coding Interview Prep include 4 lezioni in totale.

Isole definite da un valore che cambia

La variante di gaps-and-islands più rilevante dal punto di vista aziendale raggruppa le righe consecutive che condividono lo stesso stato, trasformando un registro eventi rumoroso in periodi di stato chiari. La richiesta tipica è: «Dato un registro degli eventi di un abbonamento, restituisca una riga per ogni periodo continuo in cui l'utente è rimasto in ciascuno stato».

Qui l'adiacenza non significa «i valori differiscono di 1». Significa che lo stato non è cambiato rispetto alla riga precedente. Una nuova isola inizia nel momento in cui lo stato cambia. È in questo caso che la tecnica basata su LAG si dimostra superiore al semplice trucco del numero di riga.

Il campione della sottoscrizione

Consideri una tabella sub_events per un utente, ordinata per data:

2026-01-01 active
2026-02-01 active
2026-03-01 paused
2026-04-01 active
2026-05-01 active

Il risultato desiderato è costituito da tre periodi di stato: active da gennaio a febbraio, paused a marzo, active da aprile a maggio. Noti che le due sequenze active sono isole separate, perché un periodo paused le interrompe. Lo stesso stato, se non è consecutivo, significa isole diverse.

CREATE TABLE sub_events (
  user_id INT, status TEXT, event_date DATE
);
INSERT INTO sub_events VALUES
 (1,'active','2026-01-01'),(1,'active','2026-02-01'),
 (1,'paused','2026-03-01'),(1,'active','2026-04-01'),
 (1,'active','2026-05-01');

Segnalare i cambiamenti di stato

Usi LAG per confrontare lo stato di ogni riga con quello della riga precedente. Quando differiscono (oppure il precedente è NULL per la prima riga), inizia una nuova isola. Emetta 1 in caso di cambiamento e 0 negli altri casi.

Ordini rigorosamente per data all'interno dell'utente. Per i nostri dati, i flag di cambiamento sono 1,0,1,1,0 e indicano i confini dei tre periodi.

SELECT
  user_id, status, event_date,
  CASE
    WHEN status = LAG(status)
      OVER (PARTITION BY user_id ORDER BY event_date)
    THEN 0 ELSE 1
  END AS is_change
FROM sub_events;

Trasformare la somma progressiva in una chiave di periodo

Come in precedenza, una somma progressiva dei flag di cambiamento produce una chiave di gruppo costante all'interno di ogni periodo di stato: per le nostre righe, 1,1,2,3,3. Ogni chiave distinta identifica un periodo continuo.

Il trucco della differenza tra numero di riga e valore non funziona in questo caso, perché lo stato non è un numero che avanza di 1; la combinazione di LAG e somma progressiva è lo strumento corretto quando l'adiacenza significa «valore invariato».

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS is_change
  FROM sub_events
)
SELECT user_id, status, event_date,
  SUM(is_change)
    OVER (PARTITION BY user_id ORDER BY event_date) AS grp
FROM flagged;

Aggregare nei periodi di stato

Raggruppi ora per user_id, status e la chiave della somma progressiva per riportare l'intervallo di ogni periodo. Includere status nel GROUP BY è sicuro perché rimane costante all'interno di un periodo e consente di selezionarlo senza un aggregato.

Il risultato è esattamente composto da tre righe: active dal 01-01 al 02-01, paused dal 03-01 al 03-01, active dal 04-01 al 05-01.

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
),
keyed AS (
  SELECT user_id, status, event_date,
    SUM(chg) OVER (PARTITION BY user_id ORDER BY event_date) AS grp
  FROM flagged
)
SELECT user_id, status,
  MIN(event_date) AS period_start,
  MAX(event_date) AS period_end
FROM keyed
GROUP BY user_id, status, grp
ORDER BY user_id, period_start;

Dagli eventi agli intervalli semiaperti

Un dettaglio sottile da colloquio: la data di un evento indica quando uno stato è iniziato, e il periodo termina effettivamente quando inizia lo stato successivo, non alla data dell'ultimo evento con lo stesso stato. La fine corretta del periodo coincide spesso con l'inizio del periodo successivo, modellato come intervallo semiaperto [start, next_start).

Calcoli l'inizio del periodo successivo con LEAD sui periodi consolidati, lasciando il periodo finale senza termine (NULL o 'current').

WITH periods AS (
  -- output of the previous collapse step
  SELECT user_id, status, period_start FROM collapsed
)
SELECT user_id, status, period_start,
  LEAD(period_start)
    OVER (PARTITION BY user_id ORDER BY period_start)
    AS period_end_exclusive
FROM periods;

Gestire stati ripetuti consecutivamente

Che cosa succede se il log contiene righe ridondanti come active, active, active, senza alcuna variazione? Il flag di variazione vale 0 per le ripetizioni, quindi la somma cumulativa le mantiene automaticamente nella stessa isola. È il comportamento desiderato: gli stati identici consecutivi vengono consolidati in un unico periodo.

Questa deduplicazione naturale delle ripetizioni è un vantaggio fondamentale del metodo basato sul flag di variazione ed è un aspetto che vale la pena evidenziare all'intervistatore.

Quando le lacune temporali devono interrompere un periodo

A volte avere lo stesso stato non è sufficiente: una grande lacuna temporale dovrebbe interrompere il periodo anche se lo stato è identico. Per esempio, active a gennaio e poi di nuovo active dopo sei mesi di inattività potrebbero essere considerati due periodi distinti.

Estenda il flag di variazione con una seconda condizione: inizi una nuova isola quando cambia lo stato oppure quando il tempo trascorso dall'evento precedente supera una soglia. In questo modo entrambe le regole di adiacenza si combinano in modo lineare.

CASE
  WHEN status = LAG(status)
         OVER (PARTITION BY user_id ORDER BY event_date)
   AND event_date - LAG(event_date)
         OVER (PARTITION BY user_id ORDER BY event_date) <= 31
  THEN 0 ELSE 1
END AS is_change

Contare i cambi di stato distinti

Una domanda di approfondimento naturale è: "Quante volte questo utente ha cambiato stato?" È semplicemente il conteggio dei flag di variazione meno il primo, che indica lo stato iniziale e non un cambio.

In modo equivalente, è il numero di periodi meno 1. La chiave della somma cumulativa codifica già questa informazione, quindi la risposta si ottiene dallo stesso meccanismo costruito per i periodi.

WITH flagged AS (
  SELECT user_id,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
)
SELECT user_id, SUM(chg) - 1 AS status_switches
FROM flagged GROUP BY user_id;

Perché qui è meglio evitare i self-join

Una soluzione con self-join per i periodi di stato dovrebbe associare ogni riga a quella vicina, rilevare le variazioni e poi ricostruire i confini: una sequenza di passaggi complessa e soggetta a errori, che diventa difficile da gestire con tre o più periodi.

La pipeline LAG-flag-somma cumulativa-GROUP BY gestisce un numero qualsiasi di periodi in un'unica scansione, senza join. Esplicitare questo contrasto, cioè un passaggio lineare rispetto a un self-join quadratico, dimostra esattamente il tipo di ragionamento senior che gli intervistatori valutano.

Un modello riutilizzabile

Memorizzi questo modello in quattro clausole: risolve l'intera famiglia delle isole di stato modificando soltanto il test di adiacenza nel CASE:

flag: CASE con LAG per rilevare una nuova isola.
key: SUM cumulativa del flag, con partizionamento e ordinamento.
collapse: GROUP BY sulla colonna di partizione, sullo stato e sulla chiave.
interval (facoltativo): LEAD per le estremità dei periodi semiaperti.

Lo stesso scheletro funziona per interi consecutivi, date e stati; cambia soltanto la condizione del CASE.

Verifica rapida

Verifichi di aver compreso la regola di raggruppamento per isole di stato.

Rispondi nella lezione interattiva

Riepilogo: isole di stato e di date

Ora è possibile risolvere la variante più completa di gaps-and-islands:

Adiacenza = stato invariato rispetto alla riga precedente; il flag cambia con LAG.
Calcolo della somma cumulativa dei flag di variazione per ottenere una chiave di gruppo per ogni periodo.
Consolidamento con GROUP BY user_id, status, key per ottenere gli intervalli dei periodi.
Utilizzo di LEAD per le estremità degli intervalli semiaperti; estensione del flag per interrompere il periodo in presenza di grandi lacune temporali.
Le righe identiche ripetute vengono consolidate automaticamente; il conteggio dei cambi si ricava dagli stessi flag.
Un unico modello riutilizzabile copre interi, date e stati: cambia soltanto il CASE.

Con questo si conclude il corso su gaps-and-islands, un indicatore affidabile di competenza senior nei colloqui SQL.

Gratis per iniziare

Impara Coding Interview Prep con un tutor IA — gratis

Scrivi ed esegui vero codice nel tuo browser, ricevi aiuto istantaneo da un tutor IA disponibile 24/7, e riprendi da dove hai lasciato sul web o nell'app.

Corsi: 90
Lezioni: 360

Inizia a imparare gratis Visualizza il corso completo

Domande Frequenti

La lezione «Isole con cambi di data e stato» è gratuita?

Sì — il testo completo di «Isole con cambi di data e stato» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso Coding Interview Prep, passa a CoddyKit PRO. Il corso Coding Interview Prep include 4 lezioni in totale.

Cosa imparerò in «Isole con cambi di data e stato»?

Raggruppare periodi consecutivi con lo stesso stato, una domanda comune sullo stato di un abbonamento Eserciti Coding Interview Prep con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare Coding Interview Prep?

Non è richiesta alcuna esperienza precedente. Coding Interview Prep su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 4 di 4.

Quanto tempo richiede la lezione «Isole con cambi di data e stato»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione Coding Interview Prep?

Sì. Ogni lezione Coding Interview Prep include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

Riconoscere un problema di gaps-and-islands
Il trucco della differenza tra numeri di riga
Trovare le lacune in una sequenza
Isole con cambi di data e stato

← Torna a Coding Interview Prep