Forberedelse til SQL-intervju · leksjon

Islands med endringer i dato og status

Gruppér sammenhengende perioder med samme status – et vanlig spørsmål om abonnementstilstand.

Leksjon 4 av 413 trinn

Islands med endringer i dato og status er en gratis leksjon i Forberedelse til SQL-intervju på CoddyKit. Dette er leksjon 4 av 4. Du kan lese hele leksjonen gratis nedenfor – og deretter øve praktisk i nettleseren med en innebygd kodeeditor og en AI-veileder som er tilgjengelig døgnet rundt. Den er en del av læringsløpet i Forberedelse til SQL-intervju, og fremdriften din synkroniseres mellom nettet og CoddyKit-appen. Kurset i Forberedelse til SQL-intervju inneholder totalt 4 leksjoner.

Sekvenser definert av en verdi som endres

Den mest forretningsrelevante varianten av hull og sammenhengende sekvenser grupperer påfølgende rader som har samme status, slik at en støyende hendelseslogg blir omgjort til ryddige statusperioder. En klassisk oppgave er: «Gitt en hendelseslogg for abonnementer, returner én rad per sammenhengende periode brukeren hadde hver status.»

Her betyr ikke naboskap «verdiene skiller seg med 1». Det betyr at statusen er uendret fra forrige rad. En ny sammenhengende sekvens begynner idet statusen endres. Det er her LAG-metoden er bedre enn det rene radnummertrikset.

Eksempel på abonnementsdata

Se for deg en tabell kalt sub_events for én bruker, sortert etter dato:

2026-01-01 aktiv
2026-02-01 aktiv
2026-03-01 satt på pause
2026-04-01 aktiv
2026-05-01 aktiv

Det ønskede resultatet er tre statusperioder: aktiv januar–februar, satt på pause i mars, og aktiv april–mai. Legg merke til at de to aktive periodene er separate sammenhengende sekvenser fordi en pause avbryter dem. Samme status, men ikke sammenhengende, betyr forskjellige sekvenser.

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');

Markere når statusen endres

Bruk LAG for å sammenligne statusen i hver rad med den forrige. Når statusene er forskjellige (eller den forrige er NULL for den første raden), begynner en ny sammenhengende sekvens. Vi setter 1 ved en endring og ellers 0.

Sorter strengt etter dato innenfor brukeren. For dataene våre blir endringsmarkeringene 1,0,1,1,0, som markerer de tre periodegrensene.

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;

Løpende sum til en periode-nøkkel

Som tidligere gir en løpende sum av endringsmarkeringene en gruppek nøkkel som er konstant innenfor hver statusperiode: 1,1,2,3,3 for radene våre. Hver unike nøkkel er én sammenhengende periode.

Trikset med forskjellen mellom radnummer og verdi fungerer ikke her fordi status ikke er et tall som øker med 1. Oppskriften med LAG og løpende sum er riktig verktøy når naboskap betyr «uendret verdi».

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;

Slå sammen til statusperioder

Bruk nå GROUP BY på user_id, status og nøkkelen fra den løpende summen for å rapportere hver periodes tidsrom. Det er trygt å ta med status i GROUP BY fordi den er konstant innenfor en periode, og da kan du velge den uten en aggregatfunksjon.

Resultatet er nøyaktig tre rader: aktiv fra 01-01 til 02-01, satt på pause fra 03-01 til 03-01, og aktiv fra 04-01 til 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;

Fra hendelser til halvåpne intervaller

Et subtilt poeng i intervjuer: En hendelsesdato markerer når en status startet, og perioden avsluttes i praksis når den neste statusen begynner – ikke på datoen for den siste hendelsen med samme status. Den korrekte slutten på perioden er ofte starten på den neste perioden, modellert som et halvåpent intervall [start, next_start).

Beregn starten på den neste perioden med LEAD over de sammenslåtte periodene, og la den siste perioden være åpen (NULL eller '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;

Håndtering av gjentatt status etter hverandre

Hva om loggen har overflødige rader som active, active, active uten noen endring mellom dem? Endringsflagget er 0 for gjentakelsene, så den løpende summen beholder dem automatisk i samme island. Det er ønsket oppførsel: Påfølgende identiske statuser slås sammen til én periode.

Denne naturlige dedupliseringen av gjentakelser er en viktig fordel med metoden med endringsflagg, og det er verdt å nevne for intervjueren.

Når tidsavbrudd skal bryte en periode

Noen ganger er ikke «samme status» nok; et stort tidsavbrudd bør også bryte perioden, selv om statusen er identisk. Aktiv i januar og deretter aktiv igjen etter seks måneders stillhet kan for eksempel regnes som to perioder.

Utvid endringsflagget med en ekstra betingelse: Start en ny island når statusen endres eller tiden siden forrige hendelse overskrider en terskel. Slik kombineres begge naboreglene på en ryddig måte.

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

Telling av statusskifter

Et naturlig oppfølgingsspørsmål er: «Hvor mange ganger skiftet denne brukeren status?» Det er ganske enkelt antallet endringsflagg minus det første flagget (som markerer starttilstanden, ikke et skifte).

Det tilsvarer også antallet perioder minus 1. Nøkkelen fra den løpende summen inneholder allerede denne informasjonen, så svaret følger av den samme mekanismen som du bygget for periodene.

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;

Hvorfor dette er bedre enn self-joins her

En løsning med self-join for statusperioder måtte koble hver rad med nab raden, oppdage endringer og deretter sette sammen grensene. Det blir en feilutsatt prosess i flere trinn som får problemer med tre eller flere perioder.

Pipeline-en med LAG, flagg, løpende sum og GROUP BY håndterer et hvilket som helst antall perioder i én gjennomgang, uten joins. Å formulere denne kontrasten – lineær behandling i én gjennomgang versus en kvadratisk self-join – er nettopp den typen seniorresonnering intervjuere belønner.

En gjenbrukbar mal

Husk denne malen med fire ledd; den løser hele familien av status-island-problemer ved at du bare endrer nabotesten i CASE:

flag: CASE med LAG for å oppdage en ny island.
key: Løpende SUM av flagget, med partisjonering og sortering.
collapse: GROUP BY på partisjonskolonnen, statusen og nøkkelen.
interval (valgfritt): LEAD for halvåpne periodeslutter.

Det samme skjelettet fungerer for påfølgende heltall, datoer og statuser; bare CASE-betingelsen endres.

Hurtigsjekk

Bekreft at du forstår regelen for gruppering av status-islands.

Besvar den i den interaktive leksjonen

Oppsummering: Status- og dato-islands

Du kan nå løse den mest omfattende varianten av gaps-and-islands:

Naboskap = statusen er uendret fra forrige rad; flagget endres med LAG.
Bruk en løpende sum av endringsflaggene som en gruppenøkkel per periode.
Slå sammen med GROUP BY user_id, status, key for å få periodenes spenn.
Bruk LEAD for halvåpne periodeslutter; utvid flagget slik at store tidsavbrudd også bryter perioden.
Gjentatte identiske rader slås automatisk sammen; antallet skifter kan beregnes fra de samme flaggene.
Én gjenbrukbar mal dekker heltall, datoer og statuser – bare CASE endres.

Dette fullfører kurset om gaps-and-islands, et pålitelig signal på seniornivå i SQL-intervjuer.

Gratis å komme i gang

Lær deg SQL med en AI-veileder – gratis

Skriv og kjør ekte kode i nettleseren, få umiddelbar hjelp fra en AI-veileder som er tilgjengelig døgnet rundt, og fortsett der du slapp – på nettet eller i appen.

Kurs: 30
Leksjoner: 120

Start læringen gratis Se hele kurset

Ofte stilte spørsmål

Er leksjonen «Islands med endringer i dato og status» gratis?

Ja – hele teksten i «Islands med endringer i dato og status» er gratis å lese her på nettet. For å øve interaktivt med en innebygd kodeeditor og en AI-veileder som er tilgjengelig døgnet rundt, og for å låse opp resten av Forberedelse til SQL-intervju-kurset, kan du oppgradere til CoddyKit PRO. Kurset i Forberedelse til SQL-intervju inneholder totalt 4 leksjoner.

Hva lærer jeg i «Islands med endringer i dato og status»?

Gruppér sammenhengende perioder med samme status – et vanlig spørsmål om abonnementstilstand. Du øver på Forberedelse til SQL-intervju med praktisk kode som du kjører direkte i nettleseren, mens en AI-veileder som er tilgjengelig døgnet rundt, svarer på spørsmålene dine mens du jobber deg gjennom leksjonen.

Trenger jeg erfaring for å begynne med Forberedelse til SQL-intervju?

Ingen tidligere erfaring er nødvendig. Forberedelse til SQL-intervju på CoddyKit er lagt opp for både nybegynnere og viderekomne, så De kan begynne her eller helt fra start og lære i Deres eget tempo. Dette er leksjon 4 av 4.

Hvor lang tid tar leksjonen «Islands med endringer i dato og status»?

De fleste CoddyKit-leksjoner tar omtrent 5–10 minutter. Hver leksjon er kort og interaktiv, slik at De gjør jevne fremskritt og kan fortsette akkurat der De slapp – både på nettet og i appen.

Kan jeg skrive og kjøre kode i denne Forberedelse til SQL-intervju-leksjonen?

Ja. Alle Forberedelse til SQL-intervju-leksjoner har en innebygd kodeeditor, slik at De kan skrive og kjøre ekte kode direkte i nettleseren og få umiddelbar tilbakemelding fra AI – uten lokal konfigurering.

Alle leksjonene i dette kurset

← Tilbake til Forberedelse til SQL-intervju