Aufgabe 16 - SQL Aggregation & Grouping (E-Sports Analytics)

Aufgabe 16 - SQL Aggregation & Grouping

Worum geht es?

In dieser Übung verdichten Sie Rohdaten zu aussagekräftigen Berichten: Sie fassen Werte mit Aggregatfunktionen zusammen, bilden Gruppen mit GROUP BY und filtern Gruppen mit HAVING (siehe Kapitel 8 - SQL-Aggregation und Gruppierung).

In dieser Übung üben Sie:

• Zusammenfassen: Daten mit Aggregatfunktionen (COUNT, SUM, AVG, MIN, MAX) verdichten.
• Gruppieren: Ergebnismengen mit GROUP BY in logische Gruppen unterteilen.
• Filtern: aggregierte Daten mit HAVING filtern und von WHERE abgrenzen.
• Analysieren: komplexe Zusammenhänge über mehrere Tabellen auswerten.

Szenario: Analyse des E-Sports-Turniers

Das große E-Sports-Turnier ist beendet und der Vorstand benötigt einen detaillierten Performance-Audit. Ihre Aufgabe ist es, die Rohdaten aus den Tabellen matches, teams, arenas und sponsorships in aussagekräftige Management-Berichte zu verwandeln.

CREATE TABLE teams (
    team_id    INT PRIMARY KEY,
    team_name  VARCHAR(50),
    region     VARCHAR(30)
);

CREATE TABLE arenas (
    arena_id   INT PRIMARY KEY,
    arena_name VARCHAR(50),
    city       VARCHAR(50),
    capacity   INT
);

CREATE TABLE matches (
    match_id   INT PRIMARY KEY,
    team_id    INT,
    arena_id   INT,
    game       VARCHAR(50),
    kills      INTEGER
);

CREATE TABLE sponsorships (
    sponsor_id   INT PRIMARY KEY,
    sponsor_name VARCHAR(50),
    team_id      INT
);

INSERT INTO teams (team_id, team_name, region) VALUES
(1, 'Cyber Vipers', 'Europe'),
(2, 'Neon Samurais', 'Asia'),
(3, 'Arctic Foxes', 'North America'),
(4, 'Desert Rats', 'Africa');

INSERT INTO arenas (arena_id, arena_name, city, capacity) VALUES
(10, 'Alpha Dome', 'Berlin', 5000),
(20, 'Cyber Stadium', 'Tokyo', 15000),
(30, 'Empty Hall', 'Unknown', 500);

INSERT INTO matches (match_id, team_id, arena_id, game, kills) VALUES
(101, 1, 10, 'Valorant', 45),
(102, 1, 20, 'League of Legends', 30),
(103, 2, 20, 'Counter-Strike', 12),
(104, 1, 10, 'Valorant', 55),
(105, 99, 10, 'Dota 2', 55);

INSERT INTO sponsorships (sponsor_id, sponsor_name, team_id) VALUES
(501, 'Red Bull', 1),
(502, 'Logitech', 1),
(503, 'Intel', 3),
(504, 'Nvidia', NULL);

Arbeitsaufträge (SQL-Abfragen)

Teil A: Basis-Statistiken

1. Globaler Kill-Counter: Berechnen Sie die Gesamtsumme aller Kills über alle jemals gespielten Matches hinweg.
2. Turnier-Umfang: Ermitteln Sie, wie viele verschiedene Spiele (z. B. ‘Valorant’, ‘Dota 2’) aktuell in der Tabelle matches registriert sind.
3. Arena-Kapazitäten: Finden Sie die größte und die kleinste Arena-Kapazität heraus, die im System hinterlegt ist.

Teil B: Gruppierung & Verteilung

4. Regionale Präsenz: Zählen Sie, wie viele Teams in jeder Region (region) registriert sind. Sortieren Sie die Liste bitte nach der Anzahl der Teams (höchste zuerst).
5. Performance pro Spiel: Berechnen Sie für jedes Spiel die durchschnittliche Anzahl an Kills pro Match.
6. Sponsoren-Portfolio: Listen Sie jeden Teamnamen (team_name) und die Gesamtzahl der Sponsoren auf, die dieses Team unterstützen.

Teil C: Komplexe Analysen

7. High-Performance Teams: Identifizieren Sie die Namen der Teams, die über alle ihre Matches hinweg einen Durchschnitt von mehr als 40 Kills erreicht haben.
8. Arena-Auslastung: Erstellen Sie eine Liste mit den Namen aller Arenen, in denen bereits mehr als ein Match stattgefunden hat.
9. Der “Sponsor-ROI” Bericht: Suchen Sie jene Teams, die mehr als einen Sponsor haben UND zusätzlich in einer Arena mit einer Kapazität von über 10.000 Zuschauern gespielt haben.

Abgabe & Kriterien

• Abgabe der Datei aufgabe16_aggregation.sql.
• Alle Abfragen müssen auf einer PostgreSQL-Datenbank fehlerfrei ausführbar sein.
• Konsistente Nutzung von Großbuchstaben für SQL-Keywords und saubere Formatierung.

---

Wissens-Check

• Frage 1: Warum schlägt die folgende Abfrage bei der Ausführung fehl?

  SELECT region, team_name, COUNT(*)
  FROM teams
  GROUP BY region;

• Frage 2: Erläutern Sie bitte, warum man in Aufgabe 7 die Filterung nicht einfach mit WHERE AVG(kills) > 40 durchführen kann.
• Frage 3: Wie verhalten sich Aggregatfunktionen wie AVG() oder COUNT(spaltenname) gegenüber NULL-Werten in der Datenbank?

---

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