0Pricing
SQL Interview Prep · Lezione

N righe consecutive che soddisfano una condizione

Il classico schema a finestra dei «tre giorni consecutivi con vendite superiori a X».

N righe consecutive che soddisfano una condizione è una lezione SQL Interview Prep gratuita su CoddyKit. Questa è la lezione 3 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.

Un classico di LeetCode

Questo è uno dei problemi SQL più frequenti nei colloqui: "Trovare tutte le date con almeno tre giorni consecutivi in cui le vendite hanno superato una soglia," oppure il classico di LeetCode "restituire lo stadio con 3 o più righe consecutive di affluenza superiore a 100."

La struttura è sempre la stessa: una riga è valida solo se si trova all'interno di una sequenza di N righe consecutive valide. In questa lezione vengono mostrate due soluzioni semplici e la trappola in cui cadono la maggior parte dei candidati.

I dati di esempio

Utilizziamo una tabella giornaliera sales. La condizione è amount > 100. Dobbiamo restituire ogni giorno che appartiene a una sequenza di almeno 3 giorni di calendario consecutivi che soddisfano tutti la condizione.

  • sale_date — una riga per giorno
  • amount — vendite totali di quel giorno

La sottigliezza fondamentale è questa: le righe devono essere consecutive nella sequenza e, nelle versioni basate sulle date, anche nel calendario.

SELECT * FROM sales ORDER BY sale_date;
-- sale_date  | amount
-- 2024-03-01 |  120
-- 2024-03-02 |  150
-- 2024-03-03 |  130
-- 2024-03-04 |   90
-- 2024-03-05 |  200

Approccio 1: filtrare, poi creare le isole

Il metodo più robusto consiste nel conservare prima solo le righe che soddisfano la condizione, poi raggruppare le righe rimaste in isole consecutive e infine conservare le isole con lunghezza almeno pari a N.

Il primo passaggio è il filtro WHERE. Il secondo riutilizza l'ancoraggio del pattern gaps-and-islands. Poiché abbiamo filtrato prima, qui un'isola rappresenta una sequenza di giorni consecutivi che soddisfano la condizione.

WITH qualifying AS (
  SELECT sale_date
  FROM sales
  WHERE amount > 100
)
SELECT * FROM qualifying ORDER BY sale_date;

Ancorare le sequenze valide

Numeri le righe valide in base alla data e sottrai per ottenere l'ancoraggio dell'isola. Le righe consecutive nel calendario e che soddisfano tutte la condizione condivideranno lo stesso ancoraggio; ogni giorno che non soddisfa la condizione è stato rimosso, interrompendo la sequenza esattamente nel punto corretto.

WITH qualifying AS (
  SELECT sale_date
  FROM sales
  WHERE amount > 100
),
numbered AS (
  SELECT sale_date,
    ROW_NUMBER() OVER (ORDER BY sale_date) AS rn
  FROM qualifying
)
SELECT sale_date, sale_date - rn AS grp
FROM numbered;

Conservare le isole abbastanza lunghe

Raggruppi in base all'ancoraggio, conti le righe e conservi solo i gruppi con COUNT(*) >= 3. Se l'intervistatore desidera nuovamente le singole date valide, esegua un join degli ancoraggi conservati con le righe numerate.

WITH qualifying AS (
  SELECT sale_date FROM sales WHERE amount > 100
),
numbered AS (
  SELECT sale_date,
    ROW_NUMBER() OVER (ORDER BY sale_date) AS rn
  FROM qualifying
),
islands AS (
  SELECT sale_date - rn AS grp, COUNT(*) AS len
  FROM numbered
  GROUP BY sale_date - rn
  HAVING COUNT(*) >= 3
)
SELECT n.sale_date
FROM numbered n
JOIN islands i ON n.sale_date - n.rn = i.grp
ORDER BY n.sale_date;

Approccio 2: finestra COUNT scorrevole

Un approccio più elegante quando N è piccolo e fisso consiste nell'usare un frame di finestra per contare quante delle righe circostanti soddisfano anch'esse la condizione. Se una finestra di N righe consecutive che contiene questa riga soddisfa interamente la condizione, la riga fa parte del risultato.

Prima aggiunga un flag booleano, poi sommi quel flag su frame scorrevoli.

SELECT sale_date, amount,
  CASE WHEN amount > 100 THEN 1 ELSE 0 END AS ok
FROM sales;

Sommare su tre frame

Per una sequenza di esattamente 3 righe, una riga valida fa parte del risultato se la finestra di 3 righe che termina su di essa, è centrata su di essa oppure inizia da essa ha somma pari a 3. Calcoli le tre somme mobili e verifichi se una qualsiasi è uguale a 3.

Questa è la tecnica alla base della soluzione LeetCode 601 (Human Traffic of Stadium).

WITH flagged AS (
  SELECT sale_date, amount,
    CASE WHEN amount > 100 THEN 1 ELSE 0 END AS ok
  FROM sales
),
w AS (
  SELECT *,
    SUM(ok) OVER (ORDER BY sale_date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS s_end,
    SUM(ok) OVER (ORDER BY sale_date
      ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS s_mid,
    SUM(ok) OVER (ORDER BY sale_date
      ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS s_start
  FROM flagged
)
SELECT sale_date, amount
FROM w
WHERE ok = 1 AND (s_end = 3 OR s_mid = 3 OR s_start = 3);

La trappola dei buchi nel calendario

L'approccio basato sulla somma della finestra usa ROWS, che conta le righe del risultato adiacenti, non i giorni di calendario adiacenti. Se un giorno che non soddisfa la condizione è già stato filtrato, due righe possono essere adiacenti nel risultato senza essere consecutive nel calendario.

Lezione: applichi la finestra scorrevole all'intera serie giornaliera (non filtri in anticipo), oppure usi il metodo dell'ancoraggio della data, che rispetta intrinsecamente i buchi del calendario. Esponga questo compromesso durante il colloquio.

Generalizzare a un N qualsiasi

L'approccio 1 (filtrare e poi creare le isole) si generalizza senza difficoltà: basta modificare HAVING COUNT(*) >= N. Questo è il suo grande vantaggio rispetto alla somma su più finestre, che richiede più frame man mano che N cresce.

Per un N parametrizzato o grande, preferisca il metodo delle isole — richiede una sola modifica della soglia invece di N−1 finestre scritte a mano.

-- only the threshold changes for N = 5
HAVING COUNT(*) >= 5

Scegliere l'approccio

Una guida rapida da esporre a voce:

  • Filtrare e poi creare le isole: rispetta i buchi del calendario, si generalizza a qualsiasi N e restituisce sequenze complete — è la scelta predefinita più sicura.
  • Somma su finestra scorrevole: è elegante per un N piccolo e fisso su una serie giornaliera densa, ma occorre prestare attenzione alla trappola ROWS-vs-calendar.

Citare entrambi gli approcci e poi motivare la propria scelta è esattamente ciò che gli intervistatori di livello intermedio e senior apprezzano.

Soluzione completa

La soluzione portabile per qualsiasi N, che rispetta la consecutività del calendario e restituisce le date valide:

WITH qualifying AS (
  SELECT sale_date FROM sales WHERE amount > 100
),
numbered AS (
  SELECT sale_date,
    ROW_NUMBER() OVER (ORDER BY sale_date) AS rn
  FROM qualifying
),
islands AS (
  SELECT sale_date - rn AS grp, COUNT(*) AS len
  FROM numbered
  GROUP BY sale_date - rn
  HAVING COUNT(*) >= 3
)
SELECT n.sale_date
FROM numbered n
JOIN islands i ON n.sale_date - n.rn = i.grp
ORDER BY n.sale_date;

Verifica rapida

Individui il bug sottile.

Riepilogo

Per N righe consecutive che soddisfano una condizione:

  • Filtrare e poi creare le isole: conservi le righe valide, le ancori con date - ROW_NUMBER(), raggruppi e usi HAVING COUNT(*) >= N. Si generalizza e rispetta i buchi del calendario.
  • Somma su finestra scorrevole: contrassegni le righe e sommi su frame fissi di N righe; è elegante, ma occorre prestare attenzione al problema ROWS-vs-calendar sui dati prefiltrati.

Prossimo argomento: calcolare la serie attiva corrente di un utente alla data odierna.

Domande Frequenti

La lezione «N righe consecutive che soddisfano una condizione» è gratuita?

Sì — il testo completo di «N righe consecutive che soddisfano una condizione» è 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 «N righe consecutive che soddisfano una condizione»?

Il classico schema a finestra dei «tre giorni consecutivi con vendite superiori a X». 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 3 di 4.

Quanto tempo richiede la lezione «N righe consecutive che soddisfano una condizione»?

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