SQL Interview Prep · Lektion

Abwanderungs- und Wiederkehrabfragen

Benutzer identifizieren, die abgesprungen sind, und solche, die nach einer Pause zurückgekehrt sind.

Abwanderungs- und Wiederkehrabfragen ist eine kostenlose SQL 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 SQL Interview Prep-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der SQL Interview Prep-Kurs umfasst insgesamt 4 Lektionen.

Die Kehrseite der Retention

Während Retention misst, wer geblieben ist, misst Churn, wer gegangen ist, und Resurrection, wer zurückgekehrt ist. Interviewer kombinieren diese Themen mit Retention, weil sie zeigen, ob Sie über die Abwesenheit von Aktivität nachdenken können – und das ist schwieriger, als vorhandene Aktivität zu zählen.

Der wiederkehrende Kniff: Sie können nicht nach Zeilen filtern, die nicht existieren. Bei Churn-Abfragen geht es grundsätzlich darum, die Lücke zwischen der letzten Aktivität eines Nutzers und jetzt (oder zwischen seiner letzten und nächsten Aktivität) zu finden.

Churn präzise definieren

„Churned“ bedeutet ohne ein Zeitfenster nichts. Eine häufige Definition lautet: Ein Nutzer ist churned, wenn er in den letzten 30 Tagen keine Aktivität gezeigt hat. Der Schwellenwert von 30 Tagen ohne Aktivität ist eine fachliche Entscheidung, die Sie eindeutig festlegen müssen.

Bei Abonnementprodukten kann Churn stattdessen ein gekündigtes oder abgelaufenes Abonnement bedeuten – also eine Statusänderung statt einer Aktivitätslücke. Klären Sie vor dem Schreiben von SQL, welches Modell gilt.

Letzte Aktivität pro Nutzer

Die Grundlage für Churn über Aktivitätslücken ist das jüngste Ereignis jedes Nutzers. Gruppieren Sie nach Nutzer und verwenden Sie MAX für das Ereignisdatum.

Dieser einzelne Wert zeigt im Vergleich mit dem heutigen Datum, wie lange der Nutzer inaktiv war. Alle nachfolgenden Schritte vergleichen mit diesem Datum der letzten Aktivität.

SELECT
  user_id,
  MAX(event_at::date) AS last_active
FROM events
GROUP BY user_id;

Die Abfrage für churned Nutzer

Ein Nutzer ist churned, wenn seine letzte Aktivität mehr als 30 Tage zurückliegt. Vergleichen Sie last_active mit CURRENT_DATE - 30. Jeder Nutzer, dessen jüngstes Ereignis vor diesem Grenzwert liegt, ist inaktiv geworden.

Beachten Sie, dass die eigentliche Prüfung nach der Aggregation erfolgt: Zuerst reduzieren Sie die Daten auf eine Zeile pro Nutzer, dann prüfen Sie die Lücke. Wenn Sie rohe Ereignisse nach Datum filtern, erfahren Sie nur, wer in einem bestimmten Zeitfenster inaktiv war, nicht, wer insgesamt churned ist.

WITH last_seen AS (
  SELECT user_id, MAX(event_at::date) AS last_active
  FROM events
  GROUP BY user_id
)
SELECT user_id, last_active
FROM last_seen
WHERE last_active < CURRENT_DATE - INTERVAL '30 days';

Die Churn-Rate zählen

Die Churn-Rate ist die Anzahl der churned Nutzer geteilt durch die relevante Basis, häufig die Nutzer, die zu Beginn des Zeitraums aktiv waren. Verwenden Sie eine bedingte Aggregation, um churned Nutzer und die Gesamtzahl in einem Durchlauf zu zählen, und teilen Sie sorgfältig mit 100.0 und NULLIF.

Benennen Sie im Interview den Nenner ausdrücklich: Churn bezogen auf alle Nutzer und Churn bezogen auf zuvor aktive Nutzer sind unterschiedliche Kennzahlen.

WITH last_seen AS (
  SELECT user_id, MAX(event_at::date) AS last_active
  FROM events GROUP BY user_id
)
SELECT
  COUNT(*) FILTER (
    WHERE last_active < CURRENT_DATE - INTERVAL '30 days'
  ) AS churned,
  COUNT(*) AS total_users,
  ROUND(100.0 * COUNT(*) FILTER (
    WHERE last_active < CURRENT_DATE - INTERVAL '30 days')
    / NULLIF(COUNT(*), 0), 1) AS churn_pct
FROM last_seen;

Periodenübergreifender Churn mit Mengenlogik

Eine andere Betrachtungsweise lautet: Wer war letzten Monat aktiv, aber diesen Monat nicht? Das ist eine Mengendifferenz. Erstellen Sie die Menge der im letzten Monat aktiven Nutzer und die Menge der in diesem Monat aktiven Nutzer. Ermitteln Sie anschließend die Mitglieder der ersten Menge, die nicht in der zweiten enthalten sind.

Sie können dies mit EXCEPT, einem LEFT JOIN / IS NULL Anti-Join oder NOT EXISTS ausdrücken. Der Anti-Join ist am portabelsten und wird von Interviewern am häufigsten erwartet.

WITH last_month AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_at >= DATE '2024-04-01' AND event_at < DATE '2024-05-01'
),
this_month AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_at >= DATE '2024-05-01' AND event_at < DATE '2024-06-01'
)
SELECT user_id FROM last_month
EXCEPT
SELECT user_id FROM this_month;

Die Anti-Join-Form

Dieselbe Abfrage für den Churn in diesem Zeitraum als Anti-Join: Führen Sie die Aktiven dieses Monats per LEFT JOIN mit denen des letzten Monats zusammen und behalten Sie anschließend die Zeilen, bei denen der Treffer NULL ist. Das sind die Nutzer, die letzten Monat vorhanden, diesen Monat aber nicht mehr aktiv sind – also die abgewanderten Nutzer.

NOT EXISTS ist eine ebenso gute Lösung und behandelt NULL-Werte sicher. Weisen Sie darauf hin, dass NOT IN riskant wäre, wenn die innere Ergebnismenge NULL-Werte enthalten könnte – eine klassische Falle.

SELECT lm.user_id
FROM last_month lm
LEFT JOIN this_month tm ON tm.user_id = lm.user_id
WHERE tm.user_id IS NULL;

Reaktivierung definieren

Reaktivierung bezeichnet einen Nutzer, der abgewandert war und anschließend wieder aktiv wurde. Das typische Muster ist eine Lücke in seiner Zeitachse: aktiv, dann eine Phase der Inaktivität, die länger als der Churn-Schwellenwert dauert, und anschließend wieder aktiv.

Ein diesen Monat reaktivierter Nutzer ist also jetzt aktiv, war im letzten Zeitraum inaktiv, hatte aber in einem früheren Zeitraum bereits Aktivitäten. Das ist das Gegenstück zu Churn.

Lücken mit LAG erkennen

Die elegante Methode, Reaktivierungen zu finden, ist die LAG-Fensterfunktion: Sehen Sie für jeden Aktivitätszeitraum eines Nutzers den vorherigen aktiven Zeitraum an. Überschreitet die Lücke zwischen beiden den Schwellenwert, handelt es sich in diesem Zeitraum um eine Reaktivierung.

LAG macht einen Self-Join überflüssig und ist gut lesbar. Partitionieren Sie nach Nutzer, sortieren Sie nach dem aktiven Zeitraum und vergleichen Sie jeden Zeitraum mit seinem Vorgänger.

WITH monthly AS (
  SELECT DISTINCT user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
),
gaps AS (
  SELECT user_id, active_month,
    LAG(active_month) OVER (
      PARTITION BY user_id ORDER BY active_month
    ) AS prev_month
  FROM monthly
)
SELECT user_id, active_month AS resurrected_month
FROM gaps
WHERE prev_month IS NOT NULL
  AND active_month > prev_month + INTERVAL '1 month';

Neu vs. reaktiviert vs. beibehalten

Eine vollständige Abfrage zur Aktivitätsklassifizierung ordnet jeden Nutzer, der in diesem Zeitraum aktiv ist, einer von drei Kategorien zu: neu (keine frühere Aktivität), beibehalten (auch im letzten Zeitraum aktiv) oder reaktiviert (frühere Aktivität, aber mit einer Lücke). Der Wert prev_month aus LAG steuert alle drei Fälle.

prev_month IS NULL → neu
prev_month = active_month - 1 → beibehalten
andernfalls (eine Lücke) → reaktiviert

Diese Aufschlüsselung zu erstellen, ist eine starke und vollständige Antwort.

SELECT user_id, active_month,
  CASE
    WHEN prev_month IS NULL THEN 'new'
    WHEN active_month = prev_month + INTERVAL '1 month' THEN 'retained'
    ELSE 'resurrected'
  END AS user_state
FROM gaps;

Die NULL-Falle bei NOT IN

Eine letzte tückische Falle: Wenn Sie Churn mit WHERE user_id NOT IN (SELECT user_id FROM this_month) schreiben und diese Unterabfrage auch nur ein NULL zurückgibt, ist das gesamte Ergebnis leer, weil NOT IN bei einem Vergleich mit NULL zu UNKNOWN ausgewertet wird.

Verwenden Sie bevorzugt NOT EXISTS oder einen LEFT JOIN / IS NULL als Anti-Join; beide funktionieren mit NULL-Werten korrekt. Wenn Sie diesen Unterschied ungefragt ansprechen, ist das in Interviews zu Retention ein verlässliches Signal für Seniorität.

-- safe anti-join instead of NOT IN
SELECT lm.user_id
FROM last_month lm
WHERE NOT EXISTS (
  SELECT 1 FROM this_month tm
  WHERE tm.user_id = lm.user_id
);

Kurztest

Sie möchten die Nutzer ermitteln, die letzten Monat aktiv, diesen Monat aber nicht aktiv waren. Ein Teammitglied hat WHERE user_id NOT IN (SELECT user_id FROM this_month) geschrieben, und die Abfrage liefert null Zeilen, obwohl offensichtlich einige Nutzer abgewandert sind. Was ist die sicherste Lösung?

Zusammenfassung: Churn und Reaktivierung

Die wichtigsten Punkte zu Churn und Reaktivierung:

Definieren Sie Churn über einen Inaktivitätsschwellenwert (z. B. 30 Tage ohne Aktivität) oder über eine Änderung des Abonnementstatus – klären Sie, welche Definition gilt.
Ermitteln Sie für jeden Nutzer seine MAX(last activity) und vergleichen Sie diesen Wert anschließend mit CURRENT_DATE - threshold.
Churn von einem Zeitraum zum nächsten ist eine mengentheoretische Differenz: Verwenden Sie EXCEPT, NOT EXISTS oder einen LEFT JOIN / IS NULL als Anti-Join.
Reaktivierung ist eine Lücke in der Zeitachse. Erkennen Sie sie mit LAG, um Nutzer als neu, beibehalten oder reaktiviert zu klassifizieren.
Vermeiden Sie NOT IN, wenn NULL-Werte möglich sind – die Ergebnismenge wird sonst unbemerkt leer.

Häufig gestellte Fragen

Ist die Lektion „Abwanderungs- und Wiederkehrabfragen“ kostenlos?

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

Was lerne ich in „Abwanderungs- und Wiederkehrabfragen“?

Benutzer identifizieren, die abgesprungen sind, und solche, die nach einer Pause zurückgekehrt sind. Du übst SQL 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 SQL Interview Prep zu starten?

Keine Vorkenntnisse erforderlich. SQL 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 „Abwanderungs- und Wiederkehrabfragen“?

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 SQL Interview Prep-Lektion Code schreiben und ausführen?

Ja. Jede SQL 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

Kohorte anhand der ersten Aktion definieren
Eine Retention-Matrix erstellen
Retention an Tag N und laufende Retention
Abwanderungs- und Wiederkehrabfragen

← Zurück zu SQL Interview Prep