0Pricing
SQL Interview Prep · Lektion

Kohorte anhand der ersten Aktion definieren

Jedem Benutzer anhand des Datums seines ersten Ereignisses eine Kohorte zuweisen.

Kohorte anhand der ersten Aktion definieren ist eine kostenlose SQL Interview Prep-Lektion auf CoddyKit. Dies ist Lektion 1 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.

Warum Kohorten in Interviews vorkommen

Wenn ein Interviewer im Bereich Produktanalyse sagt: „Bauen Sie eine Kohorte auf“, prüft er, ob Sie jeden Nutzer anhand dessen einer Gruppe zuordnen können, wann er erstmals etwas getan hat, und diese Gruppe anschließend über die Zeit verfolgen können.

Eine Kohorte ist eine Gruppe von Nutzern, die im selben Zeitraum dasselbe auslösende Ereignis teilen, normalerweise ihren ersten Kauf, ihre Registrierung oder ihre erste Anmeldung. Der Vorteil von Kohorten besteht darin, dass Sie Nutzer auf einer vergleichbaren Grundlage gegenüberstellen können: Jeder Nutzer der Januar-Kohorte wird ausgehend von seinem eigenen Januar-Start gemessen.

Die erste Teilkompetenz – und die, die diese Lektion einübt – besteht darin, das Datum der ersten Aktion jedes Nutzers zuverlässig zu berechnen.

Die Quelltabelle

Fast jede Kohortenfrage beginnt mit einer Ereignistabelle: eine Zeile pro Nutzeraktion mit einem Zeitstempel. Stellen Sie sich eine Tabelle events vor:

  • user_id — wer gehandelt hat
  • event_type — was die Person getan hat
  • event_at — wann, als Zeitstempel

Klären Sie im Interview die Granularität ausdrücklich: „Gibt es eine Zeile pro Ereignis, und kann ein Nutzer mehrfach vorkommen?“ Die Antwort lautet fast immer ja. Genau deshalb benötigen Sie eine Aggregation, um auf die erste Aktion pro Nutzer zu reduzieren.

CREATE TABLE events (
  user_id    INT,
  event_type VARCHAR(50),
  event_at   TIMESTAMP
);

Erste Aktion = MIN des Zeitstempels

Der zentrale Schritt ist einfach: Gruppieren Sie nach user_id und verwenden Sie MIN(event_at). Dieses Minimum ist die erste Aktion des Nutzers, also der Zeitpunkt, der ihn einer Kohorte zuordnet.

Das ist die Antwort, die Interviewer zuerst hören möchten, bevor Sie mit aufwendigen Fensterfunktionen beginnen. Ein einfaches GROUP BY ist korrekt, gut lesbar und schnell.

SELECT
  user_id,
  MIN(event_at) AS first_action_at
FROM events
GROUP BY user_id;

Auf ein definierendes Ereignis filtern

Oft wird die Kohorte durch eine bestimmte Aktion definiert, nicht durch irgendein Ereignis. „Nutzer nach ihrem ersten Kauf in Kohorten einteilen“ bedeutet, dass Sie vor der Ermittlung des Minimums auf Kaufzeilen filtern müssen.

Setzen Sie den Filter in WHERE, damit MIN nur die passenden Zeilen berücksichtigt. Eine häufige Falle im Interview besteht darin, MIN über alle Ereignisse zu bilden und erst danach zu filtern. Dadurch würde jedem Nutzer, der vor dem Kauf gestöbert hat, das falsche Startdatum zugewiesen.

SELECT
  user_id,
  MIN(event_at) AS first_purchase_at
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

In einen Kohortenzeitraum einteilen

Eine Kohorte ist normalerweise ein Zeitraum, kein exakter Zeitstempel: etwa die „Kohorte 2024-03“ oder die „Woche ab 2024-03-04“. Kürzen Sie das Datum der ersten Aktion auf die Granularität des Zeitraums.

Verwenden Sie in Postgres DATE_TRUNC('month', ...). In MySQL könnten Sie DATE_FORMAT(d, '%Y-%m-01') verwenden, in SQL Server DATETRUNC(month, d) oder den ersten Tag des Monats berechnen. Nennen Sie im Interview Ihren Dialekt, damit die Syntaxwahl bewusst wirkt.

SELECT
  user_id,
  DATE_TRUNC('month', MIN(event_at)) AS cohort_month
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

In eine CTE einschließen

Die Kohortenzuordnung pro Nutzer ist ein Baustein, den Sie in Retention-Abfragen wiederverwenden werden. Fassen Sie sie daher in einer klar benannten CTE zusammen. So bleiben die nächsten Schritte lesbar und Sie zeigen dem Interviewer, dass Sie in kombinierbaren Bausteinen denken.

Von hier aus kann jede nachgelagerte Abfrage mit user_cohort verknüpft werden, um zu ermitteln, welcher Gruppe ein Nutzer angehört.

WITH user_cohort AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events
  WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT * FROM user_cohort;

Kohortengröße: Mitglieder zählen

Die erste Plausibilitätsprüfung, die ein Interviewer erwartet, ist die Kohortengröße: Wie viele Nutzer gehören zu jeder Kohorte? Gruppieren Sie die Zuordnungs-CTE nach cohort_month und zählen Sie die unterschiedlichen Nutzer.

Verwenden Sie vorsichtshalber COUNT(DISTINCT user_id), obwohl die CTE bereits eine Zeile pro Nutzer enthält. Das signalisiert, dass Sie auf die Granularität achten. Diese Zahl wird später zum Nenner für jeden Retention-Prozentsatz.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT
  cohort_month,
  COUNT(DISTINCT user_id) AS cohort_size
FROM user_cohort
GROUP BY cohort_month
ORDER BY cohort_month;

Alternative mit einer Fensterfunktion

Manchmal bitten Interviewer darum, das Kohortenlabel an jede Ereigniszeile anzuhängen, statt eine zusammengefasste Tabelle zu erstellen. Hier spielt eine Fensterfunktion ihre Stärken aus: MIN(event_at) OVER (PARTITION BY user_id) berechnet die erste Aktion, ohne Zeilen zu entfernen.

Das ist hilfreich, wenn Sie in einem Durchlauf sowohl die einzelnen Ereignisse als auch das Kohortenlabel benötigen – genau die Grundlage für das Zählen der Retention.

SELECT
  user_id,
  event_at,
  DATE_TRUNC('month',
    MIN(event_at) OVER (PARTITION BY user_id)
  ) AS cohort_month
FROM events
WHERE event_type = 'purchase';

Die Falle bei Gleichständen und Duplikaten

Was passiert, wenn ein Nutzer zwei Ereignisse mit exakt demselben frühesten Zeitstempel hat? MIN behandelt das problemlos: Es gibt unabhängig von der Anzahl gleichrangiger Zeilen denselben kleinsten Wert zurück, sodass die Zuordnung zur Kohorte weiterhin einmal pro Nutzer erfolgt.

Vergleichen Sie das mit dem Ansatz ROW_NUMBER() ... ORDER BY event_at: Bei Gleichständen wird die Reihenfolge beliebig festgelegt, und Sie müssen einen deterministischen Tiebreaker wie event_id hinzufügen, um ein stabiles Ergebnis zu erhalten. Wenn Sie diesen Unterschied ungefragt erwähnen, wirkt das erfahren.

SELECT user_id, event_at,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY event_at, event_id
  ) AS rn
FROM events
WHERE event_type = 'purchase';

Zeitzonen und die Tagesgrenze

Eine subtile Frage im Interview: Ein Kauf um 23:30 Uhr in New York findet in UTC bereits am nächsten Tag statt. Wenn Kohorten nach Kalendertagen eingeteilt werden, entscheidet die Zeitzone, in welcher Kohorte der Nutzer landet.

Die sichere Antwort lautet: Speichern Sie Zeitstempel in UTC und wandeln Sie sie vor dem Kürzen in die Zeitzone des Geschäftsbereichs um. Sagen Sie ausdrücklich, welche Zeitzone den „Tag“ für die Kennzahl definiert, denn diese eine Entscheidung kann Tausende Nutzer zwischen Kohorten verschieben.

SELECT
  user_id,
  DATE_TRUNC('day',
    MIN(event_at AT TIME ZONE 'America/New_York')
  ) AS cohort_day
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

Nutzer vor dem Zeitfenster ausschließen

Reale Analysen begrenzen die Kohorte auf einen Datumsbereich, zum Beispiel „Kohorten, die in Q1 gestartet sind“. Filtern Sie nach dem aggregierten Datum der ersten Aktion. Das bedeutet: Verwenden Sie eine HAVING-Klausel oder einen äußeren Filter auf der CTE, nicht WHERE auf den Rohereignissen.

Wenn Sie Rohereignisse nach dem Datum filtern, könnte ein Nutzer, der im Dezember erstmals gekauft, aber auch in Q1 gehandelt hat, fälschlicherweise in eine Q1-Kohorte gelangen. Begrenzen Sie immer anhand der berechneten ersten Aktion.

WITH user_cohort AS (
  SELECT user_id, MIN(event_at) AS first_at
  FROM events WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT user_id, DATE_TRUNC('month', first_at) AS cohort_month
FROM user_cohort
WHERE first_at >= DATE '2024-01-01'
  AND first_at <  DATE '2024-04-01';

Kurzer Wissenscheck

Ein Interviewer fragt: „Teilen Sie jeden Nutzer anhand seines Monats des ersten Kaufs in eine Kohorte ein. Nutzer dürfen vor dem Kauf stöbern.“ Welcher Ansatz ist korrekt?

Zusammenfassung: Eine Kohorte definieren

Die wichtigsten Erkenntnisse für die Interviewfrage zur Kohortendefinition:

  • Eine Kohorte gruppiert Nutzer nach ihrer ersten passenden Aktion.
  • Berechnen Sie sie mit MIN(event_at), nachdem Sie in WHERE auf das definierende Ereignis gefiltert haben.
  • Ordnen Sie die Ergebnisse mit DATE_TRUNC (oder dem entsprechenden Befehl des jeweiligen Dialekts) einem Zeitraum zu.
  • Fassen Sie die Zuordnung zur Wiederverwendung in einer CTE zusammen; COUNT(DISTINCT user_id) liefert die Kohortengröße.
  • Achten Sie auf die Tagesgrenze der Zeitzone und begrenzen Sie Datumsbereiche anhand der berechneten ersten Aktion, niemals anhand der Rohereignisse.

Wenn Sie diesen Schritt beherrschen, wird die Retention-Matrix in der nächsten Lektion zu einem Join.

Häufig gestellte Fragen

Ist die Lektion „Kohorte anhand der ersten Aktion definieren“ kostenlos?

Ja — der vollständige Text von „Kohorte anhand der ersten Aktion definieren“ 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 „Kohorte anhand der ersten Aktion definieren“?

Jedem Benutzer anhand des Datums seines ersten Ereignisses eine Kohorte zuweisen. 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 1 von 4.

Wie lange dauert die Lektion „Kohorte anhand der ersten Aktion definieren“?

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

  1. Kohorte anhand der ersten Aktion definieren
  2. Eine Retention-Matrix erstellen
  3. Retention an Tag N und laufende Retention
  4. Abwanderungs- und Wiederkehrabfragen
← Zurück zu SQL Interview Prep