0Pricing
Coding Interview Prep · Lektion

Eine Retention-Matrix erstellen

Aktive Benutzer nach Kohorte und Periodenversatz zählen, um eine Retention-Tabelle zu erstellen.

Eine Retention-Matrix erstellen ist eine kostenlose Coding Interview Prep-Lektion auf CoddyKit. Dies ist Lektion 2 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.

Was eine Retention-Matrix ist

Auf die Definition einer Kohorte folgt die berühmte Retention-Matrix: Die Zeilen stehen für Kohorten, die Spalten für Perioden-Offsets (Monat 0, 1, 2, …), und jede Zelle zählt, wie viele Nutzer dieser Kohorte nach diesem Offset noch aktiv waren.

Interviewer lieben diese Aufgabe, weil sie Kohortenzuordnung, eine Verknüpfung mit den Aktivitätsdaten, eine Berechnung des Periodenunterschieds und eine Pivot-Tabelle miteinander verbindet. Sie ist die wohl repräsentativste Abfrage der Produktanalyse.

Die zwei Eingaben

Sie benötigen zwei Dinge: den Kohortenzeitraum jedes Nutzers (aus der vorherigen Lektion) und eine Aufzeichnung jedes aktiven Zeitraums pro Nutzer. Die Aktivität stammt aus derselben Ereignistabelle, reduziert auf die Granularität des Zeitraums.

Planen Sie die Abfrage also so: zuerst eine Kohorten-CTE, anschließend eine Aktivitäts-CTE, die auflistet, in welchen Monaten jeder Nutzer aktiv war, und danach die Verknüpfung beider CTEs.

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

Aktive Zeiträume auflisten

Die Aktivitäts-CTE beantwortet die Frage: „In welchen Monaten war jeder Nutzer aktiv?“ Kürzen Sie jedes Ereignis auf den Monat und entfernen Sie Duplikate mit DISTINCT oder GROUP BY. So ergibt ein Nutzer, der im März 40-mal aktiv war, nur eine Zeile für den März.

Diese Liste pro Nutzer und Monat verknüpfen Sie mit der Kohorte, um den Verbleib über die einzelnen Offsets hinweg zu messen.

WITH activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT * FROM activity;

Den Perioden-Offset berechnen

Das Herzstück der Matrix ist die Periodennummer: Wie viele Monate nach dem Beginn der Kohorte lag eine bestimmte Aktivität? Subtrahieren Sie den Kohortenmonat vom aktiven Monat.

In Postgres können Sie dafür die vollständigen Monate zwischen den beiden Datumswerten zählen. Eine portierbare Formel multipliziert die Jahresdifferenz mit 12 und addiert die Monatsdifferenz; viele Datenbanksysteme bieten dafür ebenfalls Hilfsfunktionen. Offset 0 bezeichnet den eigenen Startmonat der Kohorte.

-- months between two month-truncated dates (Postgres)
SELECT
  (EXTRACT(YEAR  FROM active_month) - EXTRACT(YEAR  FROM cohort_month)) * 12
+ (EXTRACT(MONTH FROM active_month) - EXTRACT(MONTH FROM cohort_month))
  AS period_number;

Kohorte mit Aktivität verknüpfen

Verknüpfen Sie die Kohorten-CTE mit der Activity-CTE über user_id. Jede Ergebniszeile bedeutet: Dieser Nutzer aus Kohorte X war bei Offset N aktiv. Die Anzahl unterschiedlicher Nutzer pro (Kohorte, Offset) bildet die Matrix in Langform.

Da jedes Kohortenmitglied in seinem eigenen Startmonat aktiv ist, sollte Offset 0 der Kohortengröße entsprechen – eine integrierte Plausibilitätsprüfung.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT c.cohort_month, a.active_month, c.user_id
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id;

Die Retention-Tabelle in Langform

Fügen Sie die Offset-Berechnung und die Aggregation hinzu. Sie haben nun ein übersichtliches Ergebnis in Langform: eine Zeile pro Kohorte und Offset mit der Anzahl der zurückgekehrten Nutzer. Viele Interviewer akzeptieren dieses Format direkt, weil das Pivotieren nur eine kosmetische Änderung ist.

Beachten Sie, dass der Offset-Ausdruck sowohl in SELECT als auch in GROUP BY erscheint, da er berechnet und nicht als Spalte gespeichert wird.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT
  c.cohort_month,
  (EXTRACT(YEAR FROM a.active_month)-EXTRACT(YEAR FROM c.cohort_month))*12
  +(EXTRACT(MONTH FROM a.active_month)-EXTRACT(MONTH FROM c.cohort_month)) AS period_number,
  COUNT(DISTINCT c.user_id) AS retained_users
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id
GROUP BY c.cohort_month, period_number
ORDER BY c.cohort_month, period_number;

Offsets in breite Spalten pivotieren

Für das klassische Raster pivotieren Sie die Offsets mithilfe einer bedingten Aggregation in Spalten: eine SUM-Aggregation mit einem CASE pro Offset. Dieses portable Muster funktioniert in jedem SQL-Dialekt ohne spezielle PIVOT-Syntax.

Jedes CASE gibt 1 aus, wenn die period_number der Zeile dieser Spalte entspricht. Die SUM-Aggregation zählt dadurch die für diesen Offset zurückgekehrten Nutzer.

SELECT
  cohort_month,
  COUNT(DISTINCT CASE WHEN period_number = 0 THEN user_id END) AS m0,
  COUNT(DISTINCT CASE WHEN period_number = 1 THEN user_id END) AS m1,
  COUNT(DISTINCT CASE WHEN period_number = 2 THEN user_id END) AS m2,
  COUNT(DISTINCT CASE WHEN period_number = 3 THEN user_id END) AS m3
FROM retention_long
GROUP BY cohort_month
ORDER BY cohort_month;

Von Zählwerten zu Retention-Raten

In Interviews werden normalerweise Prozentsätze und keine Rohzählwerte erwartet. Teilen Sie die Anzahl der für den jeweiligen Offset zurückgekehrten Nutzer durch die Kohortengröße (Offset 0). Konvertieren Sie einen Wert in einen Gleitkommawert oder multiplizieren Sie mit 1.0, um Ganzzahldivision zu vermeiden – den häufigsten stillen Fehler an dieser Stelle.

Das Ergebnis ist eine Retention-Kurve: 100 % in Monat 0, die sich einem Plateau annähert. Dieses Plateau ist die Kennzahl, die Stakeholder tatsächlich interessiert.

SELECT
  cohort_month,
  period_number,
  retained_users,
  ROUND(
    100.0 * retained_users
    / MAX(retained_users) OVER (PARTITION BY cohort_month),
    1
  ) AS retention_pct
FROM retention_long
ORDER BY cohort_month, period_number;

Die Falle der Ganzzahldivision

Ein garantiertes Interviewproblem: In den meisten Engines ergibt 120 / 500 den Wert 0 und nicht 0,24, weil beide Operanden Ganzzahlen sind. Retention-Prozentsätze werden dadurch unbemerkt durchgehend zu null.

Beheben Sie das, indem Sie eine Seite numerisch machen: Multiplizieren Sie mit 100.0, CASTen Sie einen Operanden nach NUMERIC oder teilen Sie durch NULLIF(size, 0), um zusätzlich eine leere Kohorte abzufangen. Wenn Sie erwähnen, dass „NULLIF eine Division durch null verhindert“, bringt das Bonuspunkte.

SELECT
  retained_users,
  cohort_size,
  100.0 * retained_users / NULLIF(cohort_size, 0) AS pct
FROM retention_long;

Fehlende Offsets mit 0 auffüllen

Wenn eine Kohorte bei Offset 2 keine zurückgekehrten Nutzer hatte, erzeugt der JOIN keine Zeile, sodass eine Lücke in der Matrix entsteht. Um explizit 0 anzuzeigen, erzeugen Sie das vollständige Raster aller Kombinationen aus (Kohorte, Offset) und verknüpfen die Zählwerte per LEFT JOIN damit.

Erstellen Sie das Raster, indem Sie Kohorten per CROSS JOIN mit einer Liste von Zahlen bzw. Offsets verknüpfen, und setzen Sie die fehlenden Zählwerte anschließend mit COALESCE auf null. Interviewer schätzen es, wenn Sie diese Lücke bemerken.

WITH offsets AS (SELECT generate_series(0, 6) AS period_number),
cohorts AS (SELECT DISTINCT cohort_month FROM retention_long)
SELECT
  c.cohort_month, o.period_number,
  COALESCE(r.retained_users, 0) AS retained_users
FROM cohorts c
CROSS JOIN offsets o
LEFT JOIN retention_long r
  ON r.cohort_month = c.cohort_month
 AND r.period_number = o.period_number
ORDER BY c.cohort_month, o.period_number;

Dreiecksform und Aktualitätsverzerrung

Ein weiterer wichtiger Punkt: Die Matrix hat eine Dreiecksform. Eine Kohorte, die erst im letzten Monat gestartet ist, kann noch keinen Wert für Monat 3 haben; deshalb tragen zu späteren Offsets weniger Kohorten bei.

Der Vergleich des Durchschnitts einer Spalte über alle Kohorten ist dadurch zugunsten älterer Kohorten verzerrt. Erwähnen Sie, dass Sie entweder das Dreieck unverfälscht anzeigen oder Vergleiche auf Offsets beschränken würden, die jede Kohorte erreicht hat. Dieses Bewusstsein unterscheidet Analysten von reinen Query-Schreibern.

Schnelltest

Ihre Retention-Abfrage teilt die Anzahl der zurückgekehrten Nutzer durch die Kohortengröße, aber jeder Prozentsatz außer dem für Monat 0 wird als 0 ausgegeben. Was ist die wahrscheinlichste Ursache?

Zusammenfassung: Die Retention-Matrix

So erstellen Sie in einem Interview eine Retention-Matrix:

  • Weisen Sie jedem Nutzer eine Kohortenperiode zu und listen Sie die aktiven Perioden jedes Nutzers dedupliziert auf.
  • Verknüpfen Sie beide Mengen und berechnen Sie den Periodenoffset (die Anzahl der Monate zwischen Kohorte und Aktivität).
  • Aggregieren Sie in Langform mit COUNT(DISTINCT user_id); pivotieren Sie bei Bedarf mit CASE zu einem Raster.
  • Wandeln Sie Zählwerte sorgfältig in Raten um und vermeiden Sie Ganzzahldivision sowie Division durch null mit 100.0 und NULLIF.
  • Verknüpfen Sie ein erzeugtes Raster per LEFT JOIN, um Zellen mit null aufzufüllen, und denken Sie daran, dass die Matrix dreieckig ist.

Häufig gestellte Fragen

Ist die Lektion „Eine Retention-Matrix erstellen“ kostenlos?

Ja — der vollständige Text von „Eine Retention-Matrix erstellen“ 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 „Eine Retention-Matrix erstellen“?

Aktive Benutzer nach Kohorte und Periodenversatz zählen, um eine Retention-Tabelle zu erstellen. 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 2 von 4.

Wie lange dauert die Lektion „Eine Retention-Matrix erstellen“?

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

  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 Coding Interview Prep