Förberedelse inför kodningsintervjuer · Lektion

Öar med datum- och statusändringar

Gruppera sammanhängande perioder med samma status – en vanlig fråga om prenumerationstillstånd

Lektion 4 av 413 steg

Öar med datum- och statusändringar är en gratis lektion i Förberedelse inför kodningsintervjuer på CoddyKit. Detta är lektion 4 av 4. Ni kan läsa hela lektionen gratis nedan och sedan öva praktiskt i webbläsaren med en inbyggd kodredigerare och en AI-handledare som är tillgänglig dygnet runt. Den ingår i lärvägen för Förberedelse inför kodningsintervjuer, och Era framsteg synkroniseras mellan webben och CoddyKit-appen. Kursen i Förberedelse inför kodningsintervjuer innehåller totalt 4 lektioner.

Öar definierade av värden som ändras

Den mest verksamhetsrelevanta varianten av luckor och öar grupperar sammanhängande rader med samma status och omvandlar en brusig händelselogg till tydliga statusperioder. En klassisk fråga är: "Givet en händelselogg för en prenumeration, returnera en rad per sammanhängande period då användaren befann sig i varje status."

Här innebär angränsning inte att värdena skiljer sig med 1. Det innebär att statusen är oförändrad jämfört med föregående rad. En ny ö börjar så snart statusen ändras. Det är här den LAG-baserade tekniken är bättre än det rena radnummertricket.

Exempel på prenumerationsdata

Anta en tabell sub_events för en användare, sorterad efter datum:

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

Det önskade resultatet är tre statusperioder: active januari–februari, paused mars och active april–maj. Observera att de två perioderna med active är separata öar eftersom en paused-period avbryter dem. Samma status, men inte i följd, innebär olika öar.

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

Markera statusändringar

Använd LAG för att jämföra varje rads status med den föregående. När de skiljer sig åt (eller när föregående värde är NULL för den första raden) börjar en ny ö. Vi anger 1 vid en ändring och annars 0.

Sortera strikt efter datum inom användaren. För våra data blir ändringsmarkeringarna 1,0,1,1,0, vilket markerar de tre periodgränserna.

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öpande summa som periodnyckel

Precis som tidigare ger en löpande summa av ändringsmarkeringarna en gruppnyckel som är konstant inom varje statusperiod: 1,1,2,3,3 för våra rader. Varje unik nyckel motsvarar en sammanhängande period.

Skillnaden mellan värde och radnummer fungerar inte här eftersom status inte är ett tal som ökar med 1. Receptet med LAG och löpande summa är rätt verktyg när angränsning betyder "oförändrat värde".

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;

Sammanfatta till statusperioder

Använd nu GROUP BY på user_id, status och nyckeln för den löpande summan för att rapportera varje periods intervall. Det är säkert att inkludera status i GROUP BY eftersom den är konstant inom en period, och då kan du välja den utan en aggregatfunktion.

Resultatet blir exakt tre rader: active 01-01 till 02-01, paused 03-01 till 03-01 och active 04-01 till 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;

Från händelser till halvöppna intervall

En subtil intervjupunkt: ett händelsedatum anger när en status började, och perioden slutar egentligen när nästa status börjar, inte på datumet för den senaste händelsen med samma status. Det korrekta slutet för perioden är ofta början på nästa period, modellerat som ett halvöppet intervall [start, next_start).

Beräkna nästa periods start med LEAD över de sammanfogade perioderna, och låt den sista perioden förbli öppen (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;

Hantera upprepade statusar i följd

Vad händer om loggen innehåller överflödiga rader som active, active, active utan någon förändring mellan dem? Ändringsflaggan är 0 för upprepningarna, så den löpande summan behåller dem automatiskt i samma ö. Det är det önskade beteendet: på varandra följande identiska statusar slås ihop till en enda period.

Denna naturliga deduplicering av upprepningar är en viktig fördel med metoden med ändringsflagga och är värd att nämna för intervjuaren.

När tidsluckor ska bryta en period

Ibland räcker det inte att statusen är densamma; en stor tidslucka ska också bryta perioden, även om statusen är identisk. Exempelvis kan active i januari och active igen efter sex månaders tystnad räknas som två perioder.

Utöka ändringsflaggan med ett andra villkor: starta en ny ö när statusen ändras eller tiden sedan föregående händelse överskrider en tröskel. På så sätt kombineras båda reglerna för angränsning på ett tydligt sätt.

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

Räkna distinkta statusbyten

En naturlig följdfråga är: "Hur många gånger bytte den här användaren status?" Det är helt enkelt antalet ändringsflaggor minus den allra första (som markerar det initiala tillståndet, inte ett byte).

Alternativt är det antalet perioder minus 1. Nyckeln från den löpande summan innehåller redan denna information, så svaret fås med samma mekanism som du byggde för perioderna.

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;

Varför detta slår självjoinar här

En lösning med självjoin för statusperioder skulle behöva para ihop varje rad med sin granne, upptäcka förändringar och sedan sammanfoga gränserna – en felbenägen flerstegsprocess som får problem med tre eller fler perioder.

Pipeline-steget LAG-flag-runningsum-groupby hanterar valfritt antal perioder i en enda genomgång utan joinar. Att tydligt formulera kontrasten – linjär genomgång i ett pass kontra kvadratisk självjoin – är precis den seniornivå i resonemang som intervjuare belönar.

En återanvändbar mall

Memorera denna mall med fyra satser; den löser hela familjen av statusöproblem genom att endast ändra närhetstestet i CASE:

flag: CASE med LAG för att upptäcka en ny ö.
key: löpande SUM av flaggan, partitionerad och ordnad.
collapse: GROUP BY på partitionskolumnen, statusen och nyckeln.
interval (valfritt): LEAD för halvöppna periodslut.

Samma grundstruktur fungerar för på varandra följande heltal, datum och statusvärden; bara CASE-villkoret ändras.

Snabbkontroll

Bekräfta att du har förstått regeln för gruppering av statusöar.

Besvara frågan i den interaktiva lektionen

Sammanfattning: status- och datumöar

Du kan nu lösa den mest avancerade varianten av luckor och öar:

Angränsning = statusen oförändrad från föregående rad; ändringsflaggan skapas med LAG.
Skapa en löpande summa av ändringsflaggorna till en gruppnyckel per period.
Slå ihop med GROUP BY user_id, status, key för att få periodintervall.
Använd LEAD för halvöppna intervallslut; utöka flaggan så att även stora tidsluckor bryter.
Upprepade identiska rader slås ihop automatiskt; antalet statusbyten fås ur samma flaggor.
En återanvändbar mall täcker heltal, datum och statusvärden – bara CASE ändras.

Det avslutar kursen om luckor och öar, en pålitlig signal på seniornivå i SQL-intervjuer.

Gratis att börja

Lär dig Förberedelse inför kodningsintervjuer med en AI-lärare – gratis

Skriv och kör riktig kod i webbläsaren, få omedelbar hjälp av en AI-lärare dygnet runt och fortsätt där du slutade – på webben eller i appen.

Kurser: 90
Lektioner: 360

Börja lära dig gratis Visa hela kursen

Vanliga frågor

Är lektionen ”Öar med datum- och statusändringar” gratis?

Ja – hela texten till ”Öar med datum- och statusändringar” kan läsas gratis här på webben. Om Ni vill öva interaktivt med en inbyggd kodredigerare och en AI-handledare som är tillgänglig dygnet runt och låsa upp resten av kursen i Förberedelse inför kodningsintervjuer, kan Ni uppgradera till CoddyKit PRO. Kursen i Förberedelse inför kodningsintervjuer innehåller totalt 4 lektioner.

Vad lär jag mig i ”Öar med datum- och statusändringar”?

Gruppera sammanhängande perioder med samma status – en vanlig fråga om prenumerationstillstånd Ni övar på Förberedelse inför kodningsintervjuer med praktisk kod som körs direkt i webbläsaren, medan en AI-handledare som är tillgänglig dygnet runt svarar på Era frågor under lektionen.

Behöver jag någon erfarenhet för att börja lära mig Förberedelse inför kodningsintervjuer?

Du behöver inga förkunskaper. Utbildningen i Förberedelse inför kodningsintervjuer på CoddyKit är upplagd för allt från nybörjare till avancerade elever, så att du kan börja här eller från början och gå fram i din egen takt. Detta är lektion 4 av 4.

Hur lång tid tar lektionen ”Öar med datum- och statusändringar”?

De flesta CoddyKit-lektioner tar cirka 5–10 minuter. Varje lektion är kort och interaktiv, så att du gör stadiga framsteg och kan fortsätta precis där du slutade – på webben eller i appen.

Kan jag skriva och köra kod i den här Förberedelse inför kodningsintervjuer-lektionen?

Ja. Varje Förberedelse inför kodningsintervjuer-lektion innehåller en inbyggd kodredigerare, så att du kan skriva och köra riktig kod direkt i webbläsaren och få omedelbar AI-feedback – utan lokal installation.

Alla lektioner i den här kursen

Identifiera ett gaps-and-islands-problem
Tricket med radnummerskillnaden
Hitta luckor i en sekvens
Öar med datum- och statusändringar

← Tillbaka till Förberedelse inför kodningsintervjuer