Aufgabe 03 - Fensterfunktionen

Rahmen & Hinweise

Datenbasis: Northwind (PostgreSQL) aus northwind.sql.
Thema: Ausschließlich Fensterfunktionen (OVER ( [PARTITION BY] [ORDER BY] [FRAME] )) wie ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, FIRST_VALUE, LAST_VALUE, SUM/AVG/COUNT/STDDEV/PERCENTILE_CONT als Fensterfunktionen etc.
Die Aufgaben sind von “Ha, easy!”, “Ok.”, “Puhh…” bis “Uiuiui!!” sortiert.

1) Laufende Summe pro Kunde

Operator: Anwenden

Formulieren Sie eine Abfrage, die pro Kunde die Bestellungen chronologisch sortiert und je Bestellung ausgibt:

order_id, order_date, Bestellwert (Summe der Positionen) und
die kumulative Summe der Bestellwerte pro Kunde (Running Total),
zusätzlich die Zeilennummer des Kaufs pro Kunde (ROW_NUMBER).

2) Top-3-Produkte je Kategorie nach Umsatz (Ranking)

Operator: Analysieren

Ermitteln Sie für jedes Produkt den Gesamtumsatz und bestimmen Sie innerhalb der jeweiligen Kategorie den Rang (RANK oder DENSE_RANK) nach Umsatz. Geben Sie nur die Top 3 je Kategorie aus (Berücksichtigung von Ties erwünscht).

3) Monat-zu-Monat-Wachstum pro Kunde

Operator: Analysieren & Interpretieren

Fassen Sie pro Kunde und Monat (z. B. date_trunc('month', order_date)) die Umsätze zusammen ohne GROUP BY in der Endabfrage (nutzen Sie Fensterfunktionen). Vergleichen Sie den Monatsumsatz mit dem Vormonat desselben Kunden über LAG. Geben Sie zusätzlich die absolute und prozentuale Veränderung aus.

4) Wiederbeschaffungsrhythmus je Produkt (Abstände & gleitender Durchschnitt)

Operator: Untersuchen

Untersuchen Sie je Produkt, in welchen Abständen (Tagen) Bestellungen eingehen. Nutzen Sie LAG(order_date) pro Produkt und berechnen Sie den Abstand in Tagen. Ermitteln Sie zusätzlich einen gleitenden Durchschnitt der letzten 5 Abstände pro Produkt über einen Zeilen-Frame (ROWS BETWEEN 4 PRECEDING AND CURRENT ROW).

5) Kunden-Kohorten: “seit erster Bestellung” & kumuliert 90 Tage

Operator: Bewerten

Bestimmen Sie pro Kunde die erste Bestellung (FIRST_VALUE(order_date)) und berechnen Sie je Bestellung die Tage seit Erstkauf. Bilden Sie die kumulative Summe der Umsätze bis Tag 90 ab Erstkauf. Markieren Sie Bestellungen innerhalb der ersten 90 Tage als “Onboarding-Phase”.

Fenster-Idee: FIRST_VALUE + SUM(...) OVER (PARTITION ...) mit ORDER BY order_date und CASE WHEN days_since_first <= 90 THEN ….

6) Verkäuferleistung je Quartal: Anteil & Rang im Team

Operator: Vergleichen & Bewerten

Berechnen Sie pro Mitarbeiter (employee_id) und Quartal den Umsatz und weisen Sie zusätzlich aus:

den kumulierten Quartalsumsatz je Mitarbeiter,
den Team-Gesamtumsatz im selben Quartal (Fenster über alle Mitarbeiter dieses Quartals),
den prozentualen Anteil eines Mitarbeiters am Teamumsatz,
den Rang des Mitarbeiters im Quartal (RANK oder PERCENT_RANK).

7) Preis-Ausreißer je Produkt

Operator: Beurteilen

Analysieren Sie je Produkt die Verteilung der tatsächlich verrechneten Positionspreise (unit_price * (1 - discount)). Berechnen Sie Mittelwert und Standardabweichung als Fensterfunktionen pro Produkt und daraus den Z-Score je Position. Flaggen Sie mögliche Ausreißer (|Z| > 2).

8) Saisonmuster pro Kategorie

Operator: Modellieren & Konstruieren

Zerlegen Sie den Umsatz je Kategorie in Trend und Saisonalität über reine Fensterlogik:

Bilden Sie für jede Kategorie Monatsumsätze über alle Jahre (nur Fensterfunktionen in der Endabfrage).
Berechnen Sie eine zentrierte 12-Monats-Gleitmittelreihe (Trend) mit einem ROWS-Frame (ROWS BETWEEN 6 PRECEDING AND 6 FOLLOWING).
Ermitteln Sie die Saisonabweichung je Monat als (Monatsumsatz - Trend) und normieren Sie diese je Kategorie mit einem weiteren Fenster so, dass die Summe der 12 Monats-Saisonfaktoren ≈ 0 (oder der Mittelwert ≈ 0).
Bestimmen Sie je Kategorie die zwei Monate mit der stärksten positiven saisonalen Abweichung (Top-2 nach Saisonfaktor, inkl. Ties) und geben Sie diese Monate mit Faktor aus.

Abgabe

Dokumentation mit:
- SQL-Befehlen
- Erläuterungen zu den Ergebnissen und Überlegungen

HTL Villach, 2025-2026,
https://www.htl-villach.at