0Pricing
SQL Interview Prep · Lezione

La serie consecutiva più lunga per utente

Calcolo della lunghezza massima della sequenza consecutiva all'interno di ciascun gruppo.

La serie consecutiva più lunga per utente è una lezione SQL Interview Prep gratuita su CoddyKit. Questa è la lezione 2 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 SQL Interview Prep, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Interview Prep include 4 lezioni in totale.

La domanda

Una domanda frequente dopo il rilevamento dei giorni consecutivi è: "Per ogni utente, qual è la sua sequenza più lunga di giorni consecutivi in cui è stato attivo?" I team di prodotto e crescita lo chiedono continuamente per misurare il coinvolgimento.

È già noto come identificare ogni sequenza. Il nuovo passaggio consiste nel trovare la lunghezza massima per utente e, spesso, nel restituire anche le date della sequenza migliore. Questa lezione si basa direttamente sullo scheletro di gaps-and-islands.

Richiamare il costruttore delle isole

Nella lezione precedente, il raggruppamento per sequenza utilizzava login_date - ROW_NUMBER() come ancoraggio dell'isola. Ogni utente può avere diverse isole; prima si calcolerà una riga per ogni isola, poi si ridurrà il risultato a una riga per utente.

Tenga presente questo piano in due livelli: prima si costruiscono le isole, poi si aggregano.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
)
SELECT user_id, login_date - rn AS grp
FROM numbered;

Una riga per ogni isola

Si consolidi ogni isola in un'unica riga riepilogativa contenente la sua durata e il relativo intervallo di date. Si raggruppi per utente e per ancoraggio, quindi si calcolino le metriche.

Questa CTE viene denominata islands, così il livello successivo può leggerla in modo chiaro.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
)
SELECT * FROM islands;

Risposta semplice: lunghezza massima

Se l'intervistatore desidera soltanto la durata, il passaggio finale è una singola istruzione: si raggruppino le isole per utente e si calcoli la durata massima.

È la risposta più chiara quando non sono necessarie le date di inizio e fine.

-- ...numbered and islands CTEs as before...
SELECT
  user_id,
  MAX(streak_len) AS longest_streak
FROM islands
GROUP BY user_id
ORDER BY user_id;

Restituire anche le date

Spesso l'intervistatore aggiunge: "e mostri quando si è verificata quella sequenza." Un semplice MAX non può indicare quale isola abbia vinto. È necessario ordinare le isole all'interno di ogni utente e conservare il rango 1.

Si utilizzi ROW_NUMBER ordinato per durata decrescente, così la sequenza migliore di ogni utente riceve il rango 1. Si aggiunga un criterio di spareggio per risolvere i pareggi in modo deterministico.

ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY streak_len DESC, streak_start ASC
) AS rnk

Ordinare e filtrare

Si racchiuda l'ordinamento in una CTE, quindi si filtrino le righe con rnk = 1. Non è possibile filtrare direttamente una funzione finestra in WHERE, quindi il livello aggiuntivo è obbligatorio.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY streak_len DESC, streak_start
    ) AS rnk
  FROM islands
)
SELECT user_id, streak_start, streak_end, streak_len
FROM ranked
WHERE rnk = 1;

RANK o ROW_NUMBER in caso di pareggio

Che cosa succede se un utente ha due sequenze di lunghezza massima uguale e l'intervistatore desidera restituirle entrambe? Si sostituisca ROW_NUMBER con RANK e si mantenga rnk = 1.

  • ROW_NUMBER — esattamente un vincitore per utente (scelto arbitrariamente in caso di pareggio, a meno di aggiungere un criterio di spareggio).
  • RANK — tutte le sequenze più lunghe a pari merito condividono il rango 1 e vengono mantenute.

Chiarisca quale comportamento è richiesto: dimostra attenzione ai casi limite.

RANK() OVER (
  PARTITION BY user_id
  ORDER BY streak_len DESC
) AS rnk  -- keep all rnk = 1

Esempio svolto

Supponga che l'utente 7 abbia effettuato l'accesso dal 1° al 4 gennaio, poi dal 10 all'11 gennaio e infine dal 20 al 23 gennaio. Si hanno tre isole di durata 4, 2 e 4. La durata massima è 4 e c'è un pareggio.

  • Con ROW_NUMBER e il criterio di spareggio streak_start: viene restituita soltanto la sequenza dal 1° al 4 gennaio.
  • Con RANK: vengono restituite entrambe le sequenze, dal 1° al 4 gennaio e dal 20 al 23 gennaio.

Esplicitare questo aspetto dimostra di aver ragionato sui duplicati.

Gestire gli utenti senza accessi

Un intervistatore potrebbe chiedere: "E gli utenti che non hanno mai effettuato l'accesso?" Questi utenti non hanno righe in logins, quindi scompaiono dal risultato. Se devono comparire con una serie pari a 0, esegua un LEFT JOIN sulla tabella completa users e usi COALESCE.

SELECT u.user_id,
  COALESCE(MAX(i.streak_len), 0) AS longest_streak
FROM users u
LEFT JOIN islands i ON i.user_id = u.user_id
GROUP BY u.user_id;

Note sulle prestazioni

Questo schema esegue una singola scansione ordinata dei dati, più un raggruppamento. Per mantenerlo veloce:

  • Si assicuri che esista un indice su (user_id, login_date), così l'ORDER BY della finestra evita un ordinamento.
  • Elimini i duplicati in anticipo se la sorgente contiene più eventi al giorno.
  • Eviti di racchiudere login_date in funzioni nell'ORDER BY, perché ciò può impedire l'uso dell'indice.

Per tabelle molto grandi, questo approccio offre prestazioni nettamente superiori a qualsiasi approccio basato su self-join.

Risposta completa da colloquio

Questa è la query completa e rifinita che restituisce la serie più lunga di ogni utente con le relative date — la versione da scrivere alla lavagna.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY streak_len DESC, streak_start
    ) AS rnk
  FROM islands
)
SELECT user_id, streak_start, streak_end, streak_len
FROM ranked
WHERE rnk = 1
ORDER BY user_id;

Verifica rapida

Scelga lo strumento corretto per il requisito.

Riepilogo

Per calcolare la serie più lunga per utente:

  • Crei le isole con l'ancoraggio login_date - ROW_NUMBER().
  • Riduca ogni isola alla lunghezza e all'intervallo di date.
  • Per ottenere solo la lunghezza, usi MAX(streak_len) raggruppando per utente.
  • Per ottenere anche le date, assegni un rango alle isole per utente e conservi il rango 1 — usi RANK per includere i pari merito, ROW_NUMBER per un solo vincitore.
  • Esegua un LEFT JOIN con users per includere gli utenti con serie pari a zero.

Prossimo argomento: individuare N righe consecutive che soddisfano una condizione.

Domande Frequenti

La lezione «La serie consecutiva più lunga per utente» è gratuita?

Sì — il testo completo di «La serie consecutiva più lunga per utente» è 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 SQL Interview Prep, passa a CoddyKit PRO. Il corso SQL Interview Prep include 4 lezioni in totale.

Cosa imparerò in «La serie consecutiva più lunga per utente»?

Calcolo della lunghezza massima della sequenza consecutiva all'interno di ciascun gruppo. Eserciti SQL 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 SQL Interview Prep?

Non è richiesta alcuna esperienza precedente. SQL 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 2 di 4.

Quanto tempo richiede la lezione «La serie consecutiva più lunga per utente»?

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 SQL Interview Prep?

Sì. Ogni lezione SQL 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

  1. Rilevare giorni consecutivi del calendario
  2. La serie consecutiva più lunga per utente
  3. N righe consecutive che soddisfano una condizione
  4. Serie consecutiva attiva aggiornata a oggi
← Torna a SQL Interview Prep