Analytics-Queries fuer Reporting-Dashboards bauen

1. Warum Reporting-Dashboards mehr als eine Window Function brauchen

Ein einzelnes AVG() OVER() oder RANK() OVER() loest genau ein Teilproblem. Echte Analytics-Queries fuer Reporting-Dashboards muessen dagegen mehrere Fragen gleichzeitig beantworten: Wie viele Nutzer einer bestimmten Kohorte sind nach drei Monaten noch aktiv? Wie hat sich der Umsatz gegenueber der Vorperiode veraendert? Diese Fragen lassen sich selten mit einer isolierten Window Function beantworten, sondern erfordern eine Pipeline aus mehreren Verarbeitungsschritten, die logisch aufeinander aufbauen.

Genau hier kommen Common Table Expressions, kurz CTEs, ins Spiel. Eine CTE, eingeleitet mit WITH name AS (...), kapselt einen Zwischenschritt der Berechnung in einem benannten, wiederverwendbaren Block. Eine typische Analytics-Query fuer ein Dashboard besteht aus drei bis fuenf solcher CTEs, die aufeinander aufbauen: Rohdaten filtern, Kohorten zuordnen, aggregieren, Window Functions fuer Vergleichswerte anwenden, und am Ende das fertige Ergebnis fuer die Anwendung formatieren.

Dieser Artikel zeigt anhand von zwei der haeufigsten Dashboard-Kennzahlen, Cohort-Retention und Period-over-Period-Wachstum, wie CTEs und Window Functions zu vollstaendigen, wartbaren Analytics-Queries kombiniert werden, und wie sich beide Muster am Ende zu einer einzigen Dashboard-Abfrage zusammenfuehren lassen.

2. CTEs als strukturelles Fundament fuer Analytics-Queries

Eine CTE ist funktional einer Subquery aehnlich, aber deutlich lesbarer, weil sie am Anfang der Abfrage benannt und danach wie eine temporaere Tabelle referenziert werden kann. Statt tief verschachtelter Subqueries, die von innen nach aussen gelesen werden muessen, liest sich eine Kette von CTEs von oben nach unten wie eine Schritt-fuer-Schritt-Anleitung: erst die Basisdaten, dann die Aggregation, dann die Vergleichsberechnung. Das ist besonders bei komplexen Analytics-Queries mit mehreren Verarbeitungsstufen ein erheblicher Lesbarkeitsgewinn.

Mehrere CTEs lassen sich in einer einzigen WITH-Klausel durch Kommas getrennt definieren, und jede spaetere CTE kann auf alle vorherigen zugreifen. PostgreSQL, MySQL ab Version 8, SQL Server und Oracle unterstuetzen diese Syntax identisch. Wichtig fuer die Performance: In PostgreSQL vor Version 12 wurden CTEs standardmaessig als Optimization Fence behandelt, das heisst der Query-Planer konnte sie nicht in die umgebende Abfrage inlinen. Ab PostgreSQL 12 entscheidet der Planer automatisch, ob eine CTE inline optimiert wird, was CTEs auch fuer performance-kritische Analytics-Queries attraktiv macht.

3. Cohort-Retention: die Kohorten-Zuordnung als erster Schritt

Eine Cohort-Retention-Analyse beantwortet die Frage, wie viele Nutzer, die im selben Zeitraum ihre erste Aktion ausgefuehrt haben, in den Folgemonaten weiterhin aktiv sind. Der erste Schritt in einer solchen Analytics-Query ist die Zuordnung jedes Nutzers zu seiner Kohorte, definiert ueber den Monat der ersten Aktivitaet. Dafuer eignet sich MIN(activity_date) gruppiert nach Nutzer, kombiniert mit einer Trunkierung auf den Monatsanfang.

Diese Kohorten-Zuordnung wird in einer eigenen CTE isoliert, weil sie in den folgenden Schritten mehrfach gebraucht wird: einmal zur Bestimmung der Kohortengroesse, einmal zur Verknuepfung jeder spaeteren Aktivitaet mit der urspruenglichen Kohorte. Diese Trennung macht die Gesamt-Query nicht nur lesbarer, sie verhindert auch, dass dieselbe Subquery mehrfach unabhaengig geschrieben und potenziell inkonsistent wird.

-- Step 1: assign every user to their signup cohort month
WITH user_cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(activity_date)) AS cohort_month
    FROM user_activity
    GROUP BY user_id
),
-- Step 2: attach the cohort month to every activity event
cohort_activity AS (
    SELECT
        uc.user_id,
        uc.cohort_month,
        DATE_TRUNC('month', ua.activity_date) AS activity_month
    FROM user_activity ua
    JOIN user_cohorts uc ON uc.user_id = ua.user_id
)
SELECT * FROM cohort_activity ORDER BY cohort_month, user_id;

4. Die Retention-Matrix mit Aggregation und Window Functions

Nach der Kohorten-Zuordnung folgt der Schritt, der die eigentliche Retention-Kennzahl berechnet: der Monatsabstand zwischen Kohortenmonat und Aktivitaetsmonat, ueblicherweise als month_number bezeichnet, wobei 0 der Kohortenmonat selbst ist, 1 der erste Folgemonat, und so weiter. Eine GROUP BY cohort_month, month_number-Aggregation zaehlt dann die aktiven Nutzer pro Kohorte und Monatsabstand.

Um daraus eine Retention-Rate statt einer absoluten Nutzerzahl zu machen, wird die Anzahl aktiver Nutzer in Monat n durch die urspruengliche Kohortengroesse in Monat 0 geteilt. Genau hier kommt eine Window Function ins Spiel: FIRST_VALUE(active_users) OVER (PARTITION BY cohort_month ORDER BY month_number) liefert die Kohortengroesse aus Monat 0 fuer jede Zeile derselben Kohorte, ohne einen zusaetzlichen Self-Join auf die Kohortengroesse zu benoetigen.

-- Step 3: count active users per cohort and month offset
WITH user_cohorts AS (
    SELECT user_id, DATE_TRUNC('month', MIN(activity_date)) AS cohort_month
    FROM user_activity GROUP BY user_id
),
cohort_activity AS (
    SELECT uc.user_id, uc.cohort_month,
           DATE_TRUNC('month', ua.activity_date) AS activity_month
    FROM user_activity ua JOIN user_cohorts uc ON uc.user_id = ua.user_id
),
monthly_counts AS (
    SELECT
        cohort_month,
        EXTRACT(YEAR FROM AGE(activity_month, cohort_month)) * 12
            + EXTRACT(MONTH FROM AGE(activity_month, cohort_month)) AS month_number,
        COUNT(DISTINCT user_id) AS active_users
    FROM cohort_activity
    GROUP BY cohort_month, activity_month
)
-- Step 4: divide by cohort size at month 0 using a window function
SELECT
    cohort_month,
    month_number,
    active_users,
    FIRST_VALUE(active_users) OVER (
        PARTITION BY cohort_month ORDER BY month_number
    ) AS cohort_size,
    ROUND(
        active_users::numeric
        / FIRST_VALUE(active_users) OVER (PARTITION BY cohort_month ORDER BY month_number),
        3
    ) AS retention_rate
FROM monthly_counts
ORDER BY cohort_month, month_number;

5. Period-over-Period-Wachstum: LAG() als Basis

Die zweite haeufige Dashboard-Kennzahl ist Period-over-Period-Wachstum, also die prozentuale Veraenderung einer Kennzahl gegenueber der vorherigen Periode. Die Window Function LAG(spalte, n) ist dafuer das direkte Werkzeug: Sie liefert den Wert derselben Spalte aus der n-ten vorherigen Zeile, sortiert nach der Zeitspalte. Mit n = 1, dem Standardwert, liefert LAG() den Wert der unmittelbar vorangegangenen Periode.

Die Wachstumsrate selbst ist eine einfache Prozentrechnung auf Basis des aktuellen und des mit LAG() geholten vorherigen Werts: (aktueller_wert - vorheriger_wert) / vorheriger_wert. Wichtig dabei ist die Division durch null abzufangen, etwa mit NULLIF(vorheriger_wert, 0), damit die Abfrage nicht mit einem Datenbankfehler abbricht, wenn eine Periode einen Ausgangswert von null hatte.

-- Month-over-month revenue growth with LAG()
SELECT
    revenue_month,
    monthly_revenue,
    LAG(monthly_revenue) OVER (ORDER BY revenue_month) AS previous_month_revenue,
    ROUND(
        (monthly_revenue - LAG(monthly_revenue) OVER (ORDER BY revenue_month))
        / NULLIF(LAG(monthly_revenue) OVER (ORDER BY revenue_month), 0)::numeric,
        4
    ) AS mom_growth_rate
FROM monthly_revenue_summary
ORDER BY revenue_month;

-- revenue_month | monthly_revenue | previous_month_revenue | mom_growth_rate
-- 2026-04-01    | 42000.00        | NULL                   | NULL
-- 2026-05-01    | 45500.00        | 42000.00                | 0.0833
-- 2026-06-01    | 43200.00        | 45500.00                | -0.0505

6. Wachstumsraten ueber mehrere Perioden und Jahresvergleiche

Fuer einen Jahresvergleich statt eines Monatsvergleichs genuegt eine Anpassung des Offsets: LAG(monthly_revenue, 12) liefert den Wert derselben Zeile ein Jahr zuvor, vorausgesetzt die Zeitreihe hat keine Luecken. Diese Flexibilitaet macht LAG() zum universellen Werkzeug fuer Year-over-Year-Vergleiche, ohne dass ein Self-Join mit einer verschobenen Datumsbedingung noetig waere.

Ein Dashboard zeigt haeufig mehrere Wachstumsraten gleichzeitig: Month-over-Month und Year-over-Year nebeneinander, um sowohl kurzfristige als auch saisonale Trends sichtbar zu machen. Beide Berechnungen lassen sich in derselben SELECT-Klausel mit unterschiedlichen LAG()-Offsets kombinieren, ohne dass die Abfrage dafuer strukturell komplexer werden muesste.

-- Combine month-over-month and year-over-year growth in one query
SELECT
    revenue_month,
    monthly_revenue,
    ROUND(
        (monthly_revenue - LAG(monthly_revenue, 1) OVER (ORDER BY revenue_month))
        / NULLIF(LAG(monthly_revenue, 1) OVER (ORDER BY revenue_month), 0)::numeric,
        4
    ) AS mom_growth_rate,
    ROUND(
        (monthly_revenue - LAG(monthly_revenue, 12) OVER (ORDER BY revenue_month))
        / NULLIF(LAG(monthly_revenue, 12) OVER (ORDER BY revenue_month), 0)::numeric,
        4
    ) AS yoy_growth_rate
FROM monthly_revenue_summary
ORDER BY revenue_month;

7. Beide Muster kombinieren: eine vollstaendige Dashboard-Query

In der Praxis lebt ein Reporting-Dashboard selten von nur einer Kennzahl. Eine realistische Dashboard-Query kombiniert Cohort-Retention und Wachstumsraten oft in separaten CTEs innerhalb derselben Abfrage, oder als getrennte Views, die vom Dashboard-Tool je nach Bedarf abgefragt werden. Der strukturelle Vorteil der CTE-Kette bleibt dabei bestehen: Jede Kennzahl entsteht in einem eigenen, klar benannten Verarbeitungsschritt, der unabhaengig getestet und bei Bedarf angepasst werden kann.

Ein bewaehrtes Muster ist, pro Dashboard-Kennzahl eine eigene View oder Materialized View auf Basis einer CTE-Kette zu erstellen, statt eine einzige monolithische Abfrage mit allen Kennzahlen gleichzeitig zu bauen. Das BI-Tool oder Frontend fragt dann gezielt die View ab, die es fuer die jeweilige Visualisierung braucht, was sowohl die Wartbarkeit als auch die Cache-Faehigkeit einzelner Kennzahlen verbessert.

-- Combined dashboard view: retention and growth as separate CTEs
CREATE MATERIALIZED VIEW dashboard_summary AS
WITH cohort_retention AS (
    SELECT cohort_month, month_number, retention_rate
    FROM retention_by_cohort
),
revenue_growth AS (
    SELECT
        revenue_month,
        monthly_revenue,
        ROUND(
            (monthly_revenue - LAG(monthly_revenue) OVER (ORDER BY revenue_month))
            / NULLIF(LAG(monthly_revenue) OVER (ORDER BY revenue_month), 0)::numeric,
            4
        ) AS mom_growth_rate
    FROM monthly_revenue_summary
)
SELECT
    r.revenue_month,
    r.monthly_revenue,
    r.mom_growth_rate,
    c.cohort_month,
    c.month_number,
    c.retention_rate
FROM revenue_growth r
LEFT JOIN cohort_retention c ON c.cohort_month = DATE_TRUNC('month', r.revenue_month);
-- Two independent metrics, joined only for a single dashboard export

8. Performance und Materialized Views fuer grosse Datenmengen

Cohort-Retention-Abfragen sind rechenintensiv, weil sie ueblicherweise ueber die gesamte Aktivitaetshistorie aller Nutzer aggregieren, oft mit einem COUNT(DISTINCT ...), das nicht trivial zu optimieren ist. Bei taeglich wachsenden Millionen Aktivitaetszeilen wird eine solche Analytics-Query live pro Dashboard-Aufruf schnell zu langsam. Eine Materialized View, die nur einmal pro Nacht oder Stunde neu berechnet wird, entkoppelt die teure Berechnung vom schnellen Lesezugriff des Dashboards.

Fuer Period-over-Period-Wachstum ist die Rechenlast meist geringer, weil die zugrunde liegenden Zeitreihen typischerweise bereits vor-aggregiert vorliegen, etwa als monatliche Umsatzsummen statt Einzeltransaktionen. Trotzdem lohnt sich auch hier ein Index auf der Zeitspalte, damit die fuer LAG() noetige Sortierung effizient aus dem Index gelesen werden kann, statt bei jeder Dashboard-Abfrage neu sortiert zu werden.

9. Wartbarkeit: CTEs vs. verschachtelte Subqueries

Der wartbarkeitsseitige Hauptvorteil von CTEs gegenueber verschachtelten Subqueries zeigt sich besonders bei Analytics-Queries, die im Laufe der Zeit wachsen. Eine tief verschachtelte Subquery-Struktur mit vier oder fuenf Ebenen wird schnell unlesbar, weil jede Aenderung an einem inneren Schritt bedeutet, mehrere Klammerebenen durchzaehlen zu muessen, um die richtige Stelle zu finden. Eine Kette benannter CTEs erlaubt es dagegen, jeden Schritt isoliert zu verstehen, zu testen und bei Bedarf durch einen einzelnen SELECT * FROM cte_name zu debuggen.

Ein weiterer praktischer Vorteil: Mehrere Window Functions, die dieselbe OVER()-Definition verwenden, koennen mit einer WINDOW-Klausel benannt und wiederverwendet werden, was Tippfehler bei wiederholten PARTITION BY- und ORDER BY-Definitionen verhindert. Kombiniert mit klar benannten CTEs entsteht so eine Analytics-Query-Struktur, die auch nach Monaten noch nachvollziehbar ist, ohne dass der urspruengliche Autor sie erneut erklaeren muss.

10. Zusammenfassung

Reale Analytics-Queries fuer Reporting-Dashboards entstehen selten aus einer einzelnen Window Function, sondern aus einer Kette von CTEs, die Rohdaten schrittweise in die gewuenschte Kennzahl transformieren. Cohort-Retention-Tabellen kombinieren eine Kohorten-Zuordnung, eine Aggregation nach Monatsabstand und FIRST_VALUE() zur Ermittlung der Kohortengroesse. Period-over-Period-Wachstum baut auf LAG() mit unterschiedlichen Offsets fuer Month-over-Month- und Year-over-Year-Vergleiche.

Fuer grosse Datenmengen entkoppeln Materialized Views die teure Berechnung vom schnellen Dashboard-Lesezugriff, waehrend gut benannte CTEs die Wartbarkeit auch bei komplexen, mehrstufigen Analytics-Queries sicherstellen. Wer diese beiden Muster, Cohort-Retention und Period-over-Period-Wachstum, einmal sauber implementiert hat, kann sie als Vorlage fuer nahezu jede weitere Dashboard-Kennzahl in reinem SQL wiederverwenden.

11. FAQ: Analytics-Queries fuer Reporting-Dashboards