Coding Interview Prep · Lektion

Inseln mit Datums- und Statusänderungen

Aufeinanderfolgende Zeiträume mit demselben Status gruppieren – eine häufige Frage zum Abonnementstatus

Inseln mit Datums- und Statusänderungen ist eine kostenlose Coding Interview Prep-Lektion auf CoddyKit. Dies ist Lektion 4 von 4. Du kannst die komplette Lektion unten kostenlos lesen – dann übst du sie direkt im Browser mit einem integrierten Code-Editor und einem KI-Tutor rund um die Uhr. Sie ist Teil des Coding Interview Prep-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der Coding Interview Prep-Kurs umfasst insgesamt 4 Lektionen.

Inseln anhand eines wechselnden Werts definieren

Die für die Praxis relevanteste Variante des Lücken-und-Inseln-Problems gruppiert aufeinanderfolgende Zeilen mit demselben Status und fasst so ein unübersichtliches Ereignisprotokoll zu klaren Zustandszeiträumen zusammen. Eine klassische Aufgabenstellung lautet: „Gegeben sei ein Ereignisprotokoll eines Abonnements. Geben Sie für jeden zusammenhängenden Zeitraum, in dem der Benutzer in einem bestimmten Status blieb, eine Zeile zurück.“

Hier bedeutet Nachbarschaft nicht „Werte unterscheiden sich um 1“. Es bedeutet, dass der Status gegenüber der vorherigen Zeile unverändert ist. Eine neue Insel beginnt in dem Moment, in dem sich der Status ändert. Hier spielt die LAG-basierte Technik ihre Stärke gegenüber dem reinen Zeilennummerntrick aus.

Das Beispiel für Abonnementereignisse

Betrachten Sie für einen Benutzer eine nach Datum sortierte Tabelle sub_events:

2026-01-01 aktiv
2026-02-01 aktiv
2026-03-01 pausiert
2026-04-01 aktiv
2026-05-01 aktiv

Die gewünschte Ausgabe besteht aus drei Statuszeiträumen: aktiv im Januar und Februar, pausiert im März, aktiv im April und Mai. Beachten Sie, dass die beiden aktiven Abschnitte separate Inseln sind, weil ein pausierter Zeitraum sie unterbricht. Derselbe Status, der nicht zusammenhängend auftritt, gehört zu verschiedenen Inseln.

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

Statusänderungen markieren

Verwenden Sie LAG, um den Status jeder Zeile mit dem der vorherigen Zeile zu vergleichen. Wenn sie sich unterscheiden (oder der vorherige Wert in der ersten Zeile NULL ist), beginnt eine neue Insel. Geben Sie bei einer Änderung 1 und ansonsten 0 aus.

Sortieren Sie innerhalb des Benutzers strikt nach Datum. Für unsere Daten lauten die Änderungsmarkierungen 1,0,1,1,0 und markieren damit die Grenzen der drei Zeiträume.

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;

Mit der laufenden Summe einen Periodenschlüssel bilden

Wie zuvor ergibt eine laufende Summe der Änderungsmarkierungen einen Gruppenschlüssel, der innerhalb jedes Statuszeitraums konstant ist: Für unsere Zeilen lautet er 1,1,2,3,3. Jeder unterschiedliche Schlüssel entspricht einem zusammenhängenden Zeitraum.

Der Differenztrick mit Zeilennummern funktioniert hier nicht, weil der Status keine Zahl ist, die in Einerschritten steigt. Das Verfahren aus LAG und laufender Summe ist das richtige Werkzeug, wenn Nachbarschaft „unveränderter Wert“ bedeutet.

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;

Zu Statuszeiträumen zusammenfassen

Gruppieren Sie nun nach user_id, status und dem Schlüssel der laufenden Summe, um den Zeitraum jedes Status auszugeben. Den Status in das GROUP BY aufzunehmen ist sicher, weil er innerhalb eines Zeitraums konstant ist. Außerdem können Sie ihn dadurch ohne Aggregatfunktion auswählen.

Das Ergebnis besteht genau aus drei Zeilen: aktiv vom 01.01. bis 01.02., pausiert am 01.03., aktiv vom 01.04. bis 01.05.

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;

Von Ereignissen zu halboffenen Intervallen

Ein subtiler Punkt in Vorstellungsgesprächen: Ein Ereignisdatum markiert, wann ein Status begonnen hat, und der Zeitraum endet tatsächlich, wenn der nächste Status beginnt, nicht am Datum des letzten Ereignisses mit demselben Status. Das korrekte Ende des Zeitraums ist häufig der Beginn des nächsten Zeitraums, modelliert als halboffenes Intervall [start, next_start).

Berechnen Sie den Beginn des nächsten Zeitraums mit LEAD über die zusammengefassten Zeiträume, wobei der letzte Zeitraum offen bleibt (NULL oder '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;

Umgang mit wiederholtem Status hintereinander

Was ist, wenn das Protokoll redundante Zeilen wie active, active, active enthält, ohne dass sich dazwischen etwas ändert? Das Änderungsflag ist bei den Wiederholungen 0, sodass die laufende Summe sie automatisch in einer Insel belässt. Das ist das gewünschte Verhalten: Aufeinanderfolgende identische Statuswerte werden zu einem einzigen Zeitraum zusammengefasst.

Diese natürliche Deduplizierung von Wiederholungen ist ein entscheidender Vorteil der Methode mit Änderungsflag und sollte im Gespräch mit dem Interviewer ausdrücklich erwähnt werden.

Wann Zeitlücken einen Zeitraum unterbrechen

Manchmal reicht ein „gleicher Status“ nicht aus; eine große Zeitlücke sollte den Zeitraum ebenfalls unterbrechen, selbst wenn der Status identisch ist. Beispielsweise könnte active im Januar und dann nach sechsmonatiger Pause erneut active als zwei Zeiträume zählen.

Erweitern Sie das Änderungsflag um eine zweite Bedingung: Starten Sie eine neue Insel, wenn sich der Status ändert oder die Zeit seit dem vorherigen Ereignis einen Schwellenwert überschreitet. So lassen sich beide Regeln für die Nachbarschaft sauber kombinieren.

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

Anzahl der Statuswechsel zählen

Eine naheliegende Anschlussfrage lautet: „Wie oft hat dieser Benutzer den Status gewechselt?“ Das ist einfach die Anzahl der Änderungsflags minus dem allerersten Flag, das den Anfangszustand und keinen Wechsel kennzeichnet.

Äquivalent dazu: die Anzahl der Zeiträume minus 1. Der Schlüssel aus der laufenden Summe enthält diese Information bereits, daher ergibt sich die Antwort aus derselben Logik, die Sie für die Zeiträume aufgebaut haben.

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;

Warum diese Methode Self-Joins hier übertrifft

Eine Lösung mit einem Self-Join für Statuszeiträume müsste jede Zeile mit ihrem Nachbarn verknüpfen, Änderungen erkennen und anschließend die Grenzen zusammenführen, eine fehleranfällige Angelegenheit in mehreren Schritten, die bei drei oder mehr Zeiträumen schnell an ihre Grenzen stößt.

Die Pipeline aus LAG, Änderungsflag, laufender Summe und GROUP BY verarbeitet beliebig viele Zeiträume in einem einzigen Durchlauf ohne Joins. Diesen Gegensatz, linearer Single-Pass gegenüber quadratischem Self-Join, klar zu benennen, ist genau die Art von Senior-Level-Denken, die Interviewer honorieren.

Eine wiederverwendbare Vorlage

Prägen Sie sich diese Vorlage mit vier Klauseln ein; sie löst die gesamte Familie der Status-Insel-Probleme, indem Sie nur den Nachbarschaftstest im CASE ändern:

Flag: CASE mit LAG, um eine neue Insel zu erkennen.
Schlüssel: laufende SUMME des Flags, partitioniert und sortiert.
Zusammenfassen: GROUP BY der Partitionsspalte, des Status und des Schlüssels.
Intervall (optional): LEAD für die Enden halboffener Zeiträume.

Dasselbe Grundgerüst funktioniert für aufeinanderfolgende Ganzzahlen, Daten und Statuswerte; nur die CASE-Bedingung ändert sich.

Kurzer Test

Vergewissern Sie sich, dass Sie die Gruppierungsregel für Statusinseln verstanden haben.

Zusammenfassung: Status- und Datumsinseln

Sie können nun die anspruchsvollste Variante von Lücken und Inseln lösen:

Nachbarschaft = Status unverändert gegenüber der vorherigen Zeile; das Änderungsflag wird mit LAG ermittelt.
Erstellen Sie aus den Änderungsflags mit einer laufenden Summe einen Gruppenschlüssel pro Zeitraum.
Fassen Sie mit GROUP BY user_id, status, key zusammen, um die Zeiträume zu erhalten.
Verwenden Sie LEAD für die Enden halboffener Intervalle; erweitern Sie das Flag, um bei großen Zeitlücken zu unterbrechen.
Wiederholte identische Zeilen werden automatisch zusammengefasst; die Anzahl der Wechsel ergibt sich aus denselben Flags.
Eine wiederverwendbare Vorlage deckt Ganzzahlen, Daten und Statuswerte ab; nur die CASE-Bedingung ändert sich.

Damit ist der Kurs zu Lücken und Inseln abgeschlossen, ein zuverlässiges Signal auf Senior-Level in SQL-Interviews.

Häufig gestellte Fragen

Ist die Lektion „Inseln mit Datums- und Statusänderungen“ kostenlos?

Ja — der vollständige Text von „Inseln mit Datums- und Statusänderungen“ ist hier im Web kostenlos zu lesen. Um sie interaktiv zu üben (integrierter Code-Editor und 24/7 KI-Tutor) und den Rest des Coding Interview Prep-Kurses freizuschalten, upgrade auf CoddyKit PRO. Der Coding Interview Prep-Kurs umfasst insgesamt 4 Lektionen.

Was lerne ich in „Inseln mit Datums- und Statusänderungen“?

Aufeinanderfolgende Zeiträume mit demselben Status gruppieren – eine häufige Frage zum Abonnementstatus Du übst Coding Interview Prep mit praktischem Code, den du direkt im Browser ausführst, und ein 24/7 KI-Tutor beantwortet deine Fragen während du die Lektion bearbeitest.

Brauche ich Erfahrung, um Coding Interview Prep zu starten?

Keine Vorkenntnisse erforderlich. Coding Interview Prep auf CoddyKit ist für Anfänger bis fortgeschrittene Lernende strukturiert, sodass du hier starten oder von Anfang an beginnen und in deinem eigenen Tempo voranschreiten kannst. Dies ist Lektion 4 von 4.

Wie lange dauert die Lektion „Inseln mit Datums- und Statusänderungen“?

Die meisten CoddyKit-Lektionen dauern etwa 5–10 Minuten. Jede ist kompakt und interaktiv, sodass du stetig Fortschritte machst und genau dort weitermachst, wo du aufgehört hast – im Web und in der App.

Kann ich in dieser Coding Interview Prep-Lektion Code schreiben und ausführen?

Ja. Jede Coding Interview Prep-Lektion enthält einen integrierten Code-Editor, sodass du echten Code direkt in deinem Browser schreibst und ausführst und sofort KI-Feedback erhältst — ohne lokale Einrichtung erforderlich.

Alle Lektionen in diesem Kurs

Ein Gaps-and-Islands-Problem erkennen
Der Trick mit der Differenz von Zeilennummern
Lücken in einer Sequenz finden
Inseln mit Datums- und Statusänderungen

← Zurück zu Coding Interview Prep