0Pricing
SQL Interview Prep · Lezione

Serie consecutiva attiva aggiornata a oggi

Calcolo di una serie consecutiva in corso e azzeramento quando si verifica un'interruzione.

Serie consecutiva attiva aggiornata a oggi è una lezione SQL 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 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 di prodotto

Le funzionalità basate sulle serie (si pensi a Duolingo o Snapchat) richiedono la serie corrente, non la serie storica più lunga. La domanda del colloquio è: "Per ogni utente, per quanti giorni consecutivi, fino a oggi, è stato attivo? Reimpostare a 0 se oggi non è stato attivo."

Questo introduce una difficoltà in più: la serie deve essere ancorata a oggi (oppure a ieri, secondo una regola di tolleranza). La costruiremo partendo dalle basi del pattern gaps-and-islands che già conosce.

Definire l'attività

Chiarisca innanzitutto le regole con l'intervistatore — queste decisioni modificano la query:

  • La serie richiede attività oggi oppure è accettabile ieri (periodo di tolleranza)?
  • Gli eventi multipli nello stesso giorno vengono ridotti a un solo giorno?
  • Quale fuso orario definisce un "giorno"?

Supporremo di avere una riga per ogni giorno di attività e che la serie sia corrente se include oggi OPPURE ieri.

Creare nuovamente le isole

Riutilizzi l'ancoraggio: numeri i giorni di ciascun utente in base alla data e sottrai. Ogni sequenza consecutiva condivide lo stesso ancoraggio. La serie corrente è semplicemente l'isola il cui ultimo giorno è oggi o ieri.

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;

Individuare l'isola più recente

L'isola più recente di ogni utente è quella con il valore massimo di streak_end. Verifichiamo se la sua data finale è abbastanza recente da poter essere considerata ancora attiva.

CURRENT_DATE indica oggi. CURRENT_DATE - 1 indica ieri. Se streak_end coincide con una delle due date, la serie è attiva.

WITH /* ...numbered, islands... */
latest AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY streak_end DESC
    ) AS rn2
  FROM islands
)
SELECT * FROM latest WHERE rn2 = 1;

Applicare il controllo di recenza

Ora stabilisca se l'isola più recente è attiva. Se la sua data finale è oggi o ieri, la serie corrente è uguale alla sua lunghezza; altrimenti l'utente ha interrotto la propria serie e il valore corrente è 0.

SELECT user_id,
  CASE
    WHEN streak_end >= CURRENT_DATE - 1
    THEN streak_len
    ELSE 0
  END AS current_streak
FROM latest
WHERE rn2 = 1;

La query completa per la serie corrente

Assembli tutti i livelli: numeri le righe, crei le isole, selezioni l'ultima per utente, quindi applichi la CASE di recenza. Questa è la risposta completa.

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,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
),
latest AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY streak_end DESC
    ) AS rn2
  FROM islands
)
SELECT user_id,
  CASE WHEN streak_end >= CURRENT_DATE - 1
       THEN streak_len ELSE 0 END AS current_streak
FROM latest
WHERE rn2 = 1
ORDER BY user_id;

Impostazione rigorosa: solo oggi

Se la regola di business è rigorosa — la serie è attiva solo se l'utente è stato attivo oggi — modifichi il confronto in modo da richiedere streak_end = CURRENT_DATE.

La versione con periodo di tolleranza (>= CURRENT_DATE - 1) è più permissiva ed è comune nelle applicazioni reali, dove la giornata non è ancora terminata. Confermi sempre quale versione desidera l'intervistatore.

CASE WHEN streak_end = CURRENT_DATE
     THEN streak_len ELSE 0 END AS current_streak

Un'alternativa: procedere a ritroso

Un modello mentale diverso, che alcuni intervistatori preferiscono, consiste nel calcolare lo scarto tra ogni giorno e oggi. Un giorno fa parte della serie corrente solo se ogni giorno da quel giorno fino a oggi è presente. In modo equivalente, si confronta login_date con CURRENT_DATE - (offset from the latest).

Il metodo delle isole è generalmente più semplice, ma conoscere un'alternativa dimostra una comprensione approfondita. L'idea fondamentale è la stessa: una catena ininterrotta ancorata a oggi.

Includere gli utenti inattivi

Gli utenti assenti da logins non hanno alcuna isola e scompaiono. Se ogni utente deve restituire una serie (probabilmente 0), esegua un LEFT JOIN sull'elenco degli utenti e usi COALESCE per impostare il risultato a 0.

SELECT u.user_id,
  COALESCE(s.current_streak, 0) AS current_streak
FROM users u
LEFT JOIN current_streaks s ON s.user_id = u.user_id;

Problemi con i fusi orari

Se l'attività è memorizzata come timestamp, il significato di "oggi" dipende dal fuso orario. Converta il timestamp prima di troncarlo a una data.

  • Memorizzi e confronti in UTC, oppure converta prima nel fuso locale dell'utente.
  • Usi event_ts AT TIME ZONE 'UTC' AT TIME ZONE user_tz in Postgres, quindi esegua il cast a date.
  • Un ::date ingenuo applicato a un timestamp UTC può collocare un evento serale nel giorno di calendario errato.

Citare questo aspetto previene una classica domanda di approfondimento.

SELECT user_id,
  (event_ts AT TIME ZONE 'UTC'
             AT TIME ZONE 'America/New_York')::date AS local_day
FROM events;

Prestazioni e conclusioni

Per dashboard aggiornate di frequente, questa query viene eseguita per utente in un unico passaggio. Per mantenerla veloce:

  • Crei un indice su (user_id, login_date).
  • Limiti facoltativamente l'input alle date recenti — una serie corrente non può includere dati più vecchi della propria lunghezza, quindi una finestra mobile di, diciamo, ultimi 400 giorni è più che sufficiente.
  • Materializzi i dati giornalieri in una tabella delle serie se vengono letti molto frequentemente.

Verifica rapida

Confermi di aver compreso l'ancoraggio a "ora".

Riepilogo

Per calcolare la serie attiva corrente alla data odierna:

  • Crei le isole con l'ancoraggio login_date - ROW_NUMBER().
  • Selezioni l'isola più recente di ogni utente in base al valore massimo di streak_end.
  • Restituisca la sua lunghezza solo se streak_end è oggi (oppure ieri, con il periodo di tolleranza); in caso contrario restituisca 0.
  • Esegua un LEFT JOIN con users per includere gli account inattivi; gestisca i fusi orari prima di troncare il timestamp a una data.

Ora dispone dell'intero kit di strumenti per l'analisi delle serie: individuare sequenze, calcolare la serie più lunga, gestire condizioni su N elementi consecutivi e calcolare la serie attiva.

Domande Frequenti

La lezione «Serie consecutiva attiva aggiornata a oggi» è gratuita?

Sì — il testo completo di «Serie consecutiva attiva aggiornata a oggi» è 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 «Serie consecutiva attiva aggiornata a oggi»?

Calcolo di una serie consecutiva in corso e azzeramento quando si verifica un'interruzione. 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 4 di 4.

Quanto tempo richiede la lezione «Serie consecutiva attiva aggiornata a oggi»?

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