/*
** Olympiade (Teil 3)
*/

/*
** Aufgabe a 
**
** Erstellen Sie ein Teilnehmer-Verzeichnis. Neben den persönlichen Daten 
** der Teilnehmer sollen auch die Sportarten aufgeführt werden, 
** in denen diese antreten.   
**
** Geben Sie in diesem Verzeichnis auch diejenigen Sportler aus,
** die noch in keinem Wettbewerb gemeldet sind.

id  name                   vorname     geburtstag  land  sportart
------------------------------------------------------------------
6   Angelopoulou           Vasiliki    1987-05-30  GRE   Schwimmen
10  Boenisch               Yvonne      1980-12-29  GER   NULL
4   Buschschulte           Antje       1978-12-27  GER   Schwimmen
12  Cavazzuti              Cinzia      1970-01-01  ITA   NULL
18  Dede                   NULL        1970-01-01  GER   NULL
9   Fedotova               Irina       1970-01-01  RUS   NULL
5   Filippi                Alessia     1987-06-23  ITA   Schwimmen
13  Harel                  Barbara     1970-01-01  FRA   Schwimmen
3   Harstick               Sara        1981-01-01  GER   Schwimmen
8   Karsten                Ekaterina   1972-06-02  BLR   Schwimmen
16  Maier                  Josef       1980-01-01  GER   Rudern
17  Maier                  Josef       1982-01-01  GER   Schwimmen
7   Rutschow-Stomporowski  Katrin      1975-04-02  GER   NULL
15  Tuuxaru                Iliisquix   1970-01-01  CUB   Schwimmen
2   van Almsick            Franziska   1978-04-05  GER   Rudern
2   van Almsick            Franziska   1978-04-05  GER   Schwimmen
1   Völker                 Sandra      1974-04-01  GER   Schwimmen
14  Xeteylu                Leidisyuri  1970-01-01  CUB   Schwimmen
11  Yukhareva              Natalya     1970-01-01  RUS   Schwimmen
*/ 

-- LEFT JOIN ist wichtig
SELECT DISTINCT s_id AS id, name, vorname, geburtstag, land, sportart
FROM            sportler_alle_teilnahme_wettkampf
ORDER BY        name, vorname, geburtstag, land
;

--
-- Mit Listen-Aggregation
--
-- http://www.postgresql.org/docs/current/interactive/sql-createfunction.html
CREATE OR REPLACE FUNCTION array_append_distinct(anyarray, anyelement) RETURNS anyarray AS 
$____$
SELECT
CASE
   WHEN $1 IS NULL AND $2 IS NULL
       THEN '{}'
   WHEN $1 IS NULL
       THEN ARRAY[$2]
   WHEN $2 IS NULL
       THEN $1
   WHEN $2 = ANY($1)
       THEN $1
   ELSE
       array_append($1,$2)
END;
$____$
LANGUAGE SQL IMMUTABLE;

-- http://www.postgresql.org/docs/9.0/interactive/sql-createaggregate.html
DROP AGGREGATE IF EXISTS array_accumulate (anyelement);

CREATE AGGREGATE array_accumulate (anyelement)
(
   SFUNC = array_append_distinct,
   STYPE = anyarray
); 

SELECT   s_id AS id, name, vorname, geburtstag, land, 
         array_accumulate(sportart) AS sportarten
FROM     sportler_alle_teilnahme_wettkampf
GROUP BY s_id, name, vorname, geburtstag, land 
ORDER BY name, vorname, geburtstag, land
;

/*
** Aufgabe b 
**
** Welche Sportlerinnen erhielten im Schwimmwettbewerb 
** 'Brust 100m' der Frauen eine Medaille?

platz  id  name          vorname    geburtstag  land
----------------------------------------------------
1      6   Angelopoulou  Vasiliki   1987-05-30  GRE
1      8   Karsten       Ekaterina  1972-06-02  BLR
1      1   Völker        Sandra     1974-04-01  GER
1      11  Yukhareva     Natalya    1970-01-01  RUS
2      4   Buschschulte  Antje      1978-12-27  GER
2      5   Filippi       Alessia    1987-06-23  ITA
2      13  Harel         Barbara    1970-01-01  FRA
*/ 

SELECT   platz, s_id AS id, name, vorname, geburtstag, land
FROM     sportler_teilnahme_wettkampf
WHERE    sportart ='Schwimmen' AND wettkampfart = 'Brust 100m' AND geschlecht='w' AND
         platz BETWEEN 1 AND 3
ORDER BY platz, name, vorname, geburtstag, land
;


/*
** Aufgabe c 
**
** Welche Plätze belegt Antje Buschschulte bislang wie oft?

id  name          vorname  geburtstag  land  platz  anzahl
----------------------------------------------------------
4   Buschschulte  Antje    1978-12-27  GER   1      2
4   Buschschulte  Antje    1978-12-27  GER   2      1
*/

/* alle */

SELECT   id, name, vorname, geburtstag, land, platz, COUNT(*) AS anzahl
FROM     sportler_teilnahme
GROUP BY id, name, vorname, geburtstag, land, platz
ORDER BY name, vorname, geburtstag, land
;

/* Antje Buschschulte (genau eine) */
SELECT   id, name, vorname, geburtstag, land, platz, COUNT(*) AS anzahl
FROM     sportler_teilnahme
WHERE    name='Buschschulte' AND vorname='Antje' AND 
         land='GER'          AND geburtstag = DATE '1978-12-27'
GROUP BY id, name, vorname, geburtstag, land, platz
;

/* Antje Buschschulte (eventuell mehrere) */
SELECT   id, name, vorname, geburtstag, land, platz, COUNT(*) AS anzahl
FROM     sportler_teilnahme
WHERE    name='Buschschulte' AND vorname='Antje'
GROUP BY id, name, vorname, geburtstag, land, platz
ORDER BY geburtstag, land
;

-- etwas komplizierter Variante, aber dieselbe Idee
SELECT   id, name, vorname, geburtstag, land, platz, COUNT(*) AS anzahl
FROM     (SELECT id, name, vorname, geburtstag, land, platz
          FROM   sportler_teilnahme
          WHERE  name='Buschschulte' AND vorname='Antje'
         ) AS BuschschultePlaetze
GROUP BY id, name, vorname, geburtstag, land, platz
;


/*
** Aufgabe d
**
** Welche Sportler/-innen haben mehrere Medaillien gewonnen?
** Die Spalte 'medaillenzahl' ist eigentlich unnötig, da nicht gefragt!
**
** Lösen Sie die Aufgabe zweimal: 
**  - Einmal mit Aggregation
**  - Einmal nur mit Selektion, Projektion und Inner Join
**    (diesmal OHNE Ausgabe der Medaillenzahl!)

id  name          vorname    geburtstag  land  medaillenanzahl
--------------------------------------------------------------
4   Buschschulte  Antje      1978-12-27  GER   3
5   Filippi       Alessia    1987-06-23  ITA   2
2   van Almsick   Franziska  1978-04-05  GER   2
1   Völker        Sandra     1974-04-01  GER   4
*/

-- Die Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt).
SELECT   id, name, vorname, geburtstag, land, COUNT(*) AS medaillenanzahl
FROM     sportler_teilnahme
WHERE    platz BETWEEN 1 AND 3
GROUP BY id, name, vorname, geburtstag, land
HAVING   COUNT(*)>1
ORDER BY name, vorname, geburtstag, land
;

-- Nur einfache Projektion, Inner Join und einfache Selektion.
-- Keine Aggregation oder anderen komplexen Konstrukte.
SELECT   DISTINCT st.id, st.name, st.vorname, st.geburtstag, st.land
FROM     sportler_teilnahme st JOIN teilnahme t ON st.id = t.s_id AND st.w_id < t.w_id
WHERE        st.platz BETWEEN 1 AND 3
         AND  t.platz BETWEEN 1 AND 3
ORDER BY name, vorname, geburtstag, land
;

-- <> an Stelle von < funktioniert auch
SELECT   DISTINCT st.id, st.name, st.vorname, st.geburtstag, st.land
FROM     sportler_teilnahme st JOIN teilnahme t ON st.id = t.s_id AND st.w_id <> t.w_id
WHERE        st.platz BETWEEN 1 AND 3
         AND  t.platz BETWEEN 1 AND 3
ORDER BY name, vorname, geburtstag, land
;

-- Allerdings werden bei st.w_id <> t.w_id doppelt so viele Tupel 
-- berechnet (Duplikate!) wie bei st.w_id < t.w_id, da für jede Person, 
-- die beispielsweise die beiden Wettbewerbe 3 und 4 gewonnen hat, 
-- festgestellt wird, dass sie auch die beiden Wettbewerbe 4 und 3 
-- gewonnen hat. Dies träg aber zu der gesuchten Information 
-- "Hat die Person mindestens zwei Wettbewerbe gewonnen?" nichts bei. 


/*
** Aufgabe e
**
** Welche Sportler/-innen haben drei oder mehr Gold-Medaillien gewonnen?
** Die Spalte 'medaillenzahl' ist eigentlich unnötig, da nicht gefragt!
**
** Lösen Sie die Aufgabe zweimal: 
**  - Einmal mit Aggregation
**  - Einmal nur mit Selektion, Projektion und Inner Join 
**    (diesmal OHNE Ausgabe der Medaillenzahl!)

id  name    vorname    geburtstag  land  medaillenanzahl
--------------------------------------------------------
1   Völker  Sandra     1974-04-01  GER   4
*/

-- Die Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt).
SELECT   id, name, vorname, geburtstag, land, COUNT(*) AS medaillenanzahl
FROM     sportler_teilnahme
WHERE    platz=1
GROUP BY id, name, vorname, geburtstag, land
HAVING   COUNT(*)>=3
ORDER BY name, vorname, geburtstag, land
;

/*
** Aufgabe f
**
** Welche Sportlerin (d.h. welche Schwimmerin) hat ALLE
** Schwimmwettbewerbe der Frauen gewonnen?
** Die Spalte 'medaillenzahl' ist eigentlich unnötig, da nicht gefragt!

id  name    vorname    geburtstag  land  medaillenanzahl
--------------------------------------------------------
1   Völker  Sandra     1974-04-01  GER   4
*/

-- Die Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt).
SELECT   s_id AS id, name, vorname, geburtstag, land, COUNT(*) AS medaillenanzahl
FROM     sportler_teilnahme_wettkampf
WHERE    platz=1 AND sportart='Schwimmen' AND geschlecht = 'w'
GROUP BY s_id, name, vorname, geburtstag, land
HAVING   COUNT(*) = (SELECT COUNT(*) 
                     FROM   wettkampf
                     WHERE  sportart='Schwimmen' AND geschlecht = 'w'
                    )
ORDER BY name, vorname, geburtstag, land
;


SELECT   DISTINCT s_id AS id, name, vorname, geburtstag, land
FROM     sportler_teilnahme_wettkampf stw
WHERE    platz=1 AND sportart='Schwimmen' AND geschlecht = 'w'
         AND
         NOT EXISTS 
         (SELECT id
          FROM   wettkampf
          WHERE  sportart='Schwimmen' AND geschlecht = 'w'
          EXCEPT
          SELECT t.w_id 
          FROM   teilnahme t
          WHERE  stw.s_id = t.s_id AND t.platz=1 
         )
ORDER BY name, vorname, geburtstag, land
;

/*
** Aufgabe g
**
** Welche Sportler/-innen haben die meisten Gold-Medaillen gewonnen?
** Die Spalte 'medaillenzahl' ist eigentlich unnötig, da nicht gefragt!

id  name    vorname    geburtstag  land  medaillenanzahl
--------------------------------------------------------
1   Völker  Sandra     1974-04-01  GER   4
*/

-- Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt)
SELECT   s_id AS id, name, vorname, geburtstag, land, COUNT(*) AS medaillenanzahl
FROM     sportler_teilnahme_wettkampf
WHERE    platz=1 AND sportart='Schwimmen' AND geschlecht = 'w'
GROUP BY s_id, name, vorname, geburtstag, land
HAVING   COUNT(*) >= ALL (SELECT   COUNT(*)
                          FROM     teilnahme
                          WHERE    platz=1
                          GROUP BY s_id
                         )
ORDER BY name, vorname, geburtstag, land
;

-- Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt)
SELECT   s_id AS id, name, vorname, geburtstag, land, COUNT(*) AS medaillenanzahl
FROM     sportler_teilnahme_wettkampf
WHERE    platz=1 AND sportart='Schwimmen' AND geschlecht = 'w'
GROUP BY s_id, name, vorname, geburtstag, land
HAVING   COUNT(*) = (SELECT MAX(medaillenanzahl)
                     FROM
                       (SELECT   COUNT(*)
                        FROM     teilnahme
                        WHERE    platz=1
                        GROUP BY s_id
                       ) AS dummy(medaillenanzahl)
                     )
ORDER BY name, vorname, geburtstag, land
;

-- Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt)
SELECT   s_id AS id, name, vorname, geburtstag, land, COUNT(*) AS medaillenanzahl
FROM     sportler_teilnahme_wettkampf
WHERE    platz=1 AND sportart='Schwimmen' AND geschlecht = 'w'
GROUP BY s_id, name, vorname, geburtstag, land
HAVING   COUNT(*) = (SELECT MAX(medaillenanzahl) 
                     FROM   (SELECT COUNT(*) OVER (PARTITION BY s_id) 
                             FROM   teilnahme 
                             WHERE  platz = 1
                            ) as dummy(medaillenanzahl)
                    )
ORDER BY name, vorname, geburtstag, land
;

-- Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt)
SELECT   id, name, vorname, geburtstag, land, medaillenanzahl
FROM     (SELECT   id, name, vorname, geburtstag, land,
                   COUNT(*) AS medaillenanzahl,
                   MAX(COUNT(*)) OVER () AS max 
          FROM     sportler_teilnahme
          WHERE    platz=1
          GROUP BY id, name, vorname, geburtstag, land
         ) AS dummy
WHERE    medaillenanzahl = max
ORDER BY name, vorname, geburtstag, land
;

-- Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt)
SELECT   id, name, vorname, geburtstag, land, COUNT(*) AS medaillenanzahl
FROM     sportler_teilnahme
WHERE    platz=1
GROUP BY id, name, vorname, geburtstag, land
HAVING   COUNT(*) IN (SELECT MAX(COUNT(*)) OVER () 
                      FROM   teilnahme 
                      WHERE  platz=1 
                      GROUP  BY s_id
                     )
ORDER BY name, vorname, geburtstag, land
;

-- Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt)
SELECT   id, name, vorname, geburtstag, land, COUNT(*) AS medaillenanzahl
FROM     sportler_teilnahme
WHERE    platz=1
GROUP BY id, name, vorname, geburtstag, land
HAVING   COUNT(*) = (SELECT   MAX(COUNT(*)) OVER () 
                     FROM     teilnahme 
                     WHERE    platz=1 
                     GROUP BY s_id
                     FETCH FIRST 1 ROW ONLY
                    )
         -- LIMIT 1 ist OK, da alle Elemente der Liste denselben Wert enthalten.
         -- Evtl. besser =SOME, für den Fall, dass die Menge leer ist. 
         -- Postgres akzeptiert hier aber auch leere Mengen.
ORDER BY name, vorname, geburtstag, land
;

/* Schmutziger Trick! Funktioniert nur, wenn es genau einen Teilnehmer
   gibt, der die meisten Medaillen gewonnen hat.
   
   FETCH ... ONLY ist ein Hilfsmittel, um die Anzahl der Ausgaben 
   (z.B. für Web-Seiten) zu begrenzen. 
   
   Für die Minimums- und Maximumssuche ist diese Klausel denkbar ungeeignet:
   
   - Es wird nur ein Minimum/Maximum gefunden, auch wenn es mehrere gibt.
   - Die Sortieroperation ist sehr teuer (O(n*log(n))). Das Minimum/Maximum
     kann viel schneller bestimmt werden (O(n)).   
*/
-- Medaillenanzahl ist eigentlich unwichtig (war nicht gefragt)
SELECT   id, name, vorname, geburtstag, land, COUNT(*) AS medaillenanzahl
FROM     sportler_teilnahme
WHERE    platz=1
GROUP BY id, name, vorname, geburtstag, land
ORDER BY medaillenanzahl DESC, name, vorname, geburtstag, land
FETCH FIRST 1 ROW ONLY
;