/*
** Olympiade (Extra)
*
* Fügen Sie zunächst folgende Daten in Ihre Datenbank ein.
* (Beachten Sie: Die Ergebnisse der anderen Olympia-Aufgaben
* verändern sich dadurch und stimmen oftmals micht mehr
* mit der  zugehörigen Musterlösung überein. Daher ist
* es evtl. sinnvoll, wenn Sie für diese Aufgabe eine eigene
* Olympiadatenbank anlegen.)
*/

INSERT INTO sportler(id, name, vorname, geburtstag, geschlecht, land)
VALUES
  (101,  'Sportlerin01', 'Prima',    DATE '1971-01-01', 'w', 'GER'),
  (102,  'Sportlerin02', 'Secunda',  DATE '1971-02-02', 'w', 'GER'),
  (103,  'Sportlerin03', 'Tertia',   DATE '1971-03-03', 'w', 'GER'),
  (104,  'Sportlerin04', 'Quarta',   DATE '1971-04-04', 'w', 'GER'),
  (105,  'Sportlerin05', 'Quinta',   DATE '1971-05-05', 'w', 'GER'),
  (106,  'Sportlerin06', 'Sexta',    DATE '1971-06-06', 'w', 'GER'),
  (107,  'Sportlerin07', 'Septa',    DATE '1971-07-07', 'w', 'GER'),
  (108,  'Sportlerin08', 'Octavia',  DATE '1971-07-07', 'w', 'GER'),
  (201,  'Sportler01',   'Primus',   DATE '1970-01-01', 'm', 'GER'),
  (202,  'Sportler02',   'Sexundus', DATE '1970-02-02', 'm', 'GER'),
  (203,  'Sportler03',   'Tertius',  DATE '1970-03-03', 'm', 'FRA'),
  (204,  'Sportler04',   'Quartus',  DATE '1970-04-04', 'm', 'GER'),
  (205,  'Sportler05',   'Quintus',  DATE '1970-05-05', 'm', 'GER'),
  (206,  'Sportler06',   'Sextus',   DATE '1970-06-06', 'm', 'GER'),
  (207,  'Sportler07',   'Septus',   DATE '1970-07-07', 'm', 'GER'),
  (208,  'Sportler08',   'Oktavius', DATE '1970-08-08', 'm', 'GER'),
  (300,  'Allos',        'Protos',   DATE '1972-01-01', 'm', 'FRA')
;

INSERT INTO teilnahme (s_id, w_id, platz)
VALUES
  (101, 1, 1),
  (102, 1, 2),
  (103, 1, 3),
  (104, 1, 4),
  (105, 1, 5),
  (106, 1, 6),
  (107, 1, 7),
  (108, 1, 8),
  (300, 2, 1),
  (201, 2, 2),
  (202, 2, 3),
  (203, 2, 4),
  (204, 2, 5),
  (205, 2, 6),
  (206, 2, 7),
  (207, 2, 8),
  (208, 2, 9)
;

/*
** Extraaufgabe
**
** Geben Sie die Länder aus, die fünf Plätze hintereinander
** in einem Wettkampf belegt haben. Geben Sie zusätzlich
** jeweils die Sportart, die Wettkampfart, das Geschlecht der Sportart
** sowie die kleinste und die größte Platznummer der Fünferketter
** und die Anzahl der aufeinanderfolgenden Plätze aus:
** 
** land  sportart  wettkampfart  geschlecht  platz_min  platz_max  anzahl
** ----------------------------------------------------------------------
** GER   Judo      bis 57kg      w           1          5          5
** GER   Judo      bis 57kg      w           2          6          5
** GER   Judo      bis 57kg      w           3          7          5
** GER   Judo      bis 57kg      w           4          8          5
** GER   Judo      bis 80kg      m           5          9          5
*/

--------------------------------------------------------------------------
-- Schritt 1
--
--   Definieren zunächst eine View "st", die die Tabellen 
--   "sportler" und "teilnahme" joint (da Sie diese öfters 
--   benötigen werden):
--     st (s_id, land, w_id, platz)
--------------------------------------------------------------------------

DROP VIEW IF EXISTS st;

CREATE VIEW st (s_id, land, w_id, platz)
AS
SELECT s.id, s.land, t.w_id, t.platz
FROM   sportler s JOIN teilnahme t ON (s.id = t.s_id)
;


--------------------------------------------------------------------------
-- Schritt 2
--
--   Finden Sie nun mit Hilfe der zuvor definierten View zwei Plätze,
--   die von Sportlern desselben Landes belegt werden und deren Abstand
--   4 beträgt (genauer: 5 minus 1).
--      Tipp (https://rot13.de/):  Wbvara Fvr fg zvg fvpu fryofg.
--
-- land  sportart  wettkampfart  geschlecht  platz_min  platz_max 
-- --------------------------------------------------------------
-- GER   Judo      bis 57kg      w           1          5
-- GER   Judo      bis 57kg      w           2          6
-- GER   Judo      bis 57kg      w           3          7
-- GER   Judo      bis 57kg      w           4          8
-- GER   Judo      bis 80kg      m           2          6
-- GER   Judo      bis 80kg      m           3          7
-- GER   Judo      bis 80kg      m           5          9
--------------------------------------------------------------------------

SELECT   st1.land, w.sportart , w.wettkampfart, w.geschlecht,
         st1.platz AS platz_min, st2.platz AS platz_max
FROM          st st1 
         JOIN st st2      ON (    st2.w_id  = st1.w_id
                              AND st2.land  = st1.land
                              AND st2.platz = st1.platz+5-1
                             )
         JOIN wettkampf w ON (st1.w_id = w.id)
ORDER BY land, sportart, wettkampfart, geschlecht, platz_min
;


--------------------------------------------------------------------------
-- Schritt 3
--
--   Erweitern Sie die vorherige Anfrage. Zählen Sie, wie viele 
--   Plätze zwischen den beiden zuvor ermittelten Plätzen 
--   (einschließlich dieser beiden Plätze) vom selben Land 
--   belegt werden.
--
-- land  sportart  wettkampfart  geschlecht  platz_min  platz_max  anzahl
-- ----------------------------------------------------------------------
-- GER   Judo      bis 57kg      w           1          5          5
-- GER   Judo      bis 57kg      w           2          6          5
-- GER   Judo      bis 57kg      w           3          7          5
-- GER   Judo      bis 57kg      w           4          8          5
-- GER   Judo      bis 80kg      m           2          6          4
-- GER   Judo      bis 80kg      m           3          7          4
-- GER   Judo      bis 80kg      m           5          9          5
--
-- Diese Aufgabe kann auf unterschiedliche Weise gelöst werden:
--   - Mit einer komplexen Subquery in der Projektionsliste 
--   - Mit GROUP BY (Tipp: Wbvara Fvr fg rva qevggrf Zny zvg fvpu fryofg.)
--   - Mit einem LATERAL JOIN (vermutlich die eleganteste Lösung)
--   - Auf eine ganz andere Weise
-- Versuchen Sie (mindestens) die ersten beiden Varianten 
-- zu implementieren. 
--------------------------------------------------------------------------

--
-- Variante 1 (Subquery, die die Anzahl der Plätze vom selben Land zählt)
--
SELECT   st1.land, w.sportart, w.wettkampfart, w.geschlecht,
         st1.platz AS platz_min, st2.platz AS platz_max,
         (SELECT COUNT(*)
          FROM   st st3
          WHERE      st3.w_id = st1.w_id
                 AND st3.land = st1.land
                 AND st3.platz BETWEEN st1.platz AND st2.platz
         ) AS anzahl_gleicher_laender
FROM          st st1
         JOIN st st2      ON (    st2.w_id  = st1.w_id
                              AND st2.land  = st1.land
                              AND st2.platz = st1.platz+5-1
                             )
         JOIN wettkampf w ON (st1.w_id = w.id)
ORDER BY land, sportart, wettkampfart, geschlecht, platz_min
;

--
-- Variante 2 (GROUB BY, COUNT und HAVING)
--
SELECT   st1.land, w.sportart, w.wettkampfart, w.geschlecht,
         st1.platz AS platz_min, st2.platz AS platz_max, COUNT(*) AS anzahl
FROM          st st1
         JOIN st st2      ON (    st2.w_id  = st1.w_id
                              AND st2.land  = st1.land
                              AND st2.platz = st1.platz+5-1
                             )
         JOIN st st3      ON (    st3.w_id  = st1.w_id
                              AND st3.land  = st1.land
                              AND st3.platz BETWEEN st1.platz AND st2.platz
                             )
         JOIN wettkampf w ON (st1.w_id = w.id)
GROUP BY st1.land, w.sportart, w.wettkampfart, w.geschlecht, st1.platz, st2.platz
ORDER BY land, sportart, wettkampfart, geschlecht, platz_min
;

--
-- Variante 3 (LATERAL JOIN)
--
SELECT   st1.land, w.sportart, w.wettkampfart, w.geschlecht,
         st1.platz AS platz_min, st2.platz AS platz_max, anzahl
FROM          st st1
         JOIN st st2      ON (    st2.w_id  = st1.w_id
                              AND st2.land  = st1.land
                              AND st2.platz = st1.platz+5-1
                             )
         JOIN wettkampf w ON (st1.w_id = w.id),
         LATERAL
           (SELECT COUNT(*)
            FROM   st st3
            WHERE      st3.w_id = st1.w_id
                   AND st3.land = st1.land
                   AND st3.platz BETWEEN st1.platz AND st2.platz
           ) land_anzahl(anzahl)
ORDER BY land, sportart, wettkampfart, geschlecht, platz_min
;

--------------------------------------------------------------------------
-- Schritt 4
--
--   Selektieren Sie mittels einer WHERE- oder einer HAVING-Klausel,
--   diejenigen Wettkämpfe, für die die Anzahl gleicher Länder
--   gleich 5 ist.
--
-- land  sportart  wettkampfart  geschlecht  platz_min  platz_max  anzahl
-- ----------------------------------------------------------------------
-- GER   Judo      bis 57kg      w           1          5          5
-- GER   Judo      bis 57kg      w           2          6          5
-- GER   Judo      bis 57kg      w           3          7          5
-- GER   Judo      bis 57kg      w           4          8          5
-- GER   Judo      bis 80kg      m           5          9          5
--------------------------------------------------------------------------

--
-- Variante 1 (Subquery, die die Anzahl der Plätze vom selben Land zählt)
--
SELECT   st1.land, w.sportart, w.wettkampfart, w.geschlecht,
         st1.platz AS platz_min, st2.platz AS platz_max,
         5 as anzahl -- alternativ: st2.platz - st1.platz + 1 AS anzahl
FROM          st st1
         JOIN st st2      ON (    st2.w_id  = st1.w_id
                              AND st2.land  = st1.land
                              AND st2.platz = st1.platz+5-1
                             )
         JOIN wettkampf w ON (st1.w_id = w.id)
WHERE    5 = (SELECT COUNT(*)
              FROM   st st3
              WHERE      st3.w_id = st1.w_id
                     AND st3.land = st1.land
                     AND st3.platz BETWEEN st1.platz AND st2.platz
             )
ORDER BY land, sportart, wettkampfart, geschlecht, platz_min
;


--
-- Variante 2 (GROUB BY, COUNT und HAVING)
--
SELECT   st1.land, w.sportart, w.wettkampfart, w.geschlecht,
         st1.platz AS platz_min, st2.platz AS platz_max, 
         COUNT(*) AS anzahl -- alternativ: st2.platz - st1.platz + 1 AS anzahl
FROM          st st1
         JOIN st st2      ON (    st2.w_id  = st1.w_id
                              AND st2.land  = st1.land
                              AND st2.platz = st1.platz+5-1
                             )
         JOIN st st3      ON (    st3.w_id  = st1.w_id
                              AND st3.land  = st1.land
                              AND st3.platz BETWEEN st1.platz AND st2.platz
                             )
         JOIN wettkampf w ON (st1.w_id = w.id)
GROUP BY st1.land, w.sportart, w.wettkampfart, w.geschlecht, st1.platz, st2.platz
HAVING   COUNT(*) = 5
ORDER BY land, sportart, wettkampfart, geschlecht, platz_min
;

--
-- Variante 3 (LATERAL JOIN)
--
SELECT   st1.land, w.sportart, w.wettkampfart, w.geschlecht,
         st1.platz AS platz_min, st2.platz AS platz_max, 
         anzahl -- alternativ: st2.platz - st1.platz + 1 AS anzahl
FROM          st st1
         JOIN st st2      ON (    st2.w_id  = st1.w_id
                              AND st2.land  = st1.land
                              AND st2.platz = st1.platz+5-1
                             )
         JOIN wettkampf w ON (st1.w_id = w.id),
         LATERAL
           (SELECT COUNT(*)
            FROM   st st3
            WHERE      st3.w_id = st1.w_id
                   AND st3.land = st1.land
                   AND st3.platz BETWEEN st1.platz AND st2.platz
           ) land_anzahl(anzahl)
WHERE anzahl = 5
;

--------------------------------------------------------------------------
-- Schritt 5
--
--
--   Erweitern Sie diese Anfrage so, dass sie für alle Folgen mit
--   n Plätzen, die in einem Wettkampf von Sportlern eines Landes
--   belegt werden, Das Land, der Wettkampf, der kleinste und der
--   größte Platz sowie die Anzahl ausgegeben werden. Im Prinzip
--   müssen Sie "nur" dafür sorgen, dass die Zahl der benachbarten
--   Plätze (5 oder 4 für 5-1) nicht mehr explizit in der Anfrage
--   auftaucht. 
--
--   Tipp: Qvr Namnuy vfg fgrgf fg2.cyngm - fg1.cyngm + 1, 
--         jraa nyyr Fcbegyre iba fg1.cyngm ovf fg2.cyngm 
--         qrzfryora Ynaq natruöera.
--
-- land  sportart  wettkampfart  geschlecht  platz_min  platz_max  anzahl
-- ----------------------------------------------------------------------
-- GER   Judo      bis 57kg      w           1          2          2
-- GER   Judo      bis 57kg      w           2          3          2  
-- ...
-- GER   Schwimmen Brust 100m    w           1          2          2 
-- ...
-- GER   Judo      bis 57kg      w           1          5          5
-- GER   Judo      bis 57kg      w           2          6          5
-- GER   Judo      bis 57kg      w           3          7          5
-- GER   Judo      bis 57kg      w           4          8          5
-- GER   Judo      bis 80kg      m           5          9          5
-- ...
-- GER   Judo      bis 57kg      w           1          8          8
--
-- Insgesamt 40 Ergebnistupel
--------------------------------------------------------------------------

--
-- Variante 1 (Subquery, die die Anzahl der Plätze vom selben Land zählt)
--
SELECT   st1.land, w.sportart, w.wettkampfart, w.geschlecht,
         st1.platz AS platz_min, st2.platz AS platz_max,
         st2.platz - st1.platz + 1 AS anzahl
FROM          st st1
         JOIN st st2      ON (    st2.w_id  = st1.w_id
                              AND st2.land  = st1.land
                              AND st2.platz > st1.platz
                              AND st2.platz =   st1.platz
                                              + (SELECT COUNT(*)
                                                 FROM   st st3
                                                 WHERE      st3.w_id = st1.w_id
                                                        AND st3.land = st1.land
                                                        AND st3.platz BETWEEN 
                                                              st1.platz AND st2.platz
                                                )
                                              - 1
                             )
         JOIN wettkampf w ON (st1.w_id = w.id)
ORDER BY anzahl, land, sportart, wettkampfart, geschlecht, platz_min
;


--
-- Variante 2 (GROUB BY, COUNT und HAVING)
--
SELECT   st1.land, w.sportart, w.wettkampfart, w.geschlecht,
         st1.platz AS platz_min, st2.platz AS platz_max, 
         st2.platz - st1.platz + 1 AS anzahl  -- effizienter als COUNT(*)
FROM          st st1
         JOIN st st2      ON (    st2.w_id  = st1.w_id
                              AND st2.land  = st1.land
                              AND st2.platz > st1.platz
                             )
         JOIN st st3      ON (    st3.w_id  = st1.w_id
                              AND st3.land  = st1.land
                              AND st3.platz BETWEEN st1.platz AND st2.platz
                             )
         JOIN wettkampf w ON (st1.w_id = w.id)
GROUP BY st1.land, w.sportart, w.wettkampfart, w.geschlecht, st1.platz, st2.platz
HAVING   st2.platz = st1.platz+COUNT(*)-1 
ORDER BY anzahl, land, sportart, wettkampfart, geschlecht, platz_min
;


--
-- Variante 3 (LATERAL JOIN)
--
SELECT   st1.land, w.sportart, w.wettkampfart, w.geschlecht,
         st1.platz AS platz_min, st2.platz AS platz_max, 
         st2.platz - st1.platz + 1 AS anzahl -- effizienter als land_anzahl.anzahl
FROM          st st1
         JOIN st st2      ON (    st2.w_id  = st1.w_id
                              AND st2.land  = st1.land
                              AND st2.platz > st1.platz
                             )
         JOIN wettkampf w ON (st1.w_id = w.id),
         LATERAL
           (SELECT COUNT(*)
            FROM   st st3
            WHERE      st3.w_id = st1.w_id
                   AND st3.land = st1.land
                   AND st3.platz BETWEEN st1.platz AND st2.platz
           ) land_anzahl(anzahl)
WHERE    st2.platz = st1.platz+anzahl-1
ORDER BY anzahl, land, sportart, wettkampfart, geschlecht, platz_min
;


--------------------------------------------------------------------------
-- Schritt 6
--
--   Schreiben Sie eine View, mit der man allgemein Folgen
--   mit n (n = 2, 3, ... ) aufeinanderfolgenden Plätzen 
--   ermitteln kann, die von Sportlern eines Landes belegt werden.
--   Geben Sie testhalber die Folgen für n = 5 und n = 8 aus.
--
-- land  sportart  wettkampfart  geschlecht  platz_min  platz_max  anzahl
-- ----------------------------------------------------------------------
-- GER   Judo      bis 57kg      w           1          5          5
-- GER   Judo      bis 57kg      w           2          6          5
-- GER   Judo      bis 57kg      w           3          7          5
-- GER   Judo      bis 57kg      w           4          8          5
-- GER   Judo      bis 80kg      m           5          9          5
--
-- land  sportart  wettkampfart  geschlecht  platz_min  platz_max  anzahl
-- ----------------------------------------------------------------------
-- GER   Judo      bis 57kg      w           1          8          8
--------------------------------------------------------------------------

--
-- Variante 1 (Subquery, die die Anzahl der Plätze vom selben Land zählt)
--
DROP VIEW IF EXISTS anzahl_aufeinanderfolgender_plaetze_1;

CREATE VIEW
  anzahl_aufeinanderfolgender_plaetze_1
  (land, sportart, wettkampfart, geschlecht, platz_min, platz_max, anzahl)
AS
SELECT st1.land, w.sportart, w.wettkampfart, w.geschlecht,
       st1.platz AS platz_min, st2.platz AS platz_max,
       st2.platz - st1.platz + 1 AS anzahl
FROM        st st1
       JOIN st st2      ON (    st2.w_id  = st1.w_id
                            AND st2.land  = st1.land
                            AND st2.platz > st1.platz
                            AND st2.platz =   st1.platz
                                            + (SELECT COUNT(*)
                                               FROM   st st3
                                               WHERE      st3.w_id = st1.w_id
                                                      AND st3.land = st1.land
                                                      AND st3.platz BETWEEN 
                                                            st1.platz AND st2.platz
                                              )
                                            - 1
                           )
       JOIN wettkampf w ON (st1.w_id = w.id)
;

-- Test-Anfragen
SELECT   *
FROM     anzahl_aufeinanderfolgender_plaetze_1
WHERE    anzahl = 5
ORDER BY anzahl, land, sportart, wettkampfart, geschlecht, platz_min
;
SELECT   *
FROM     anzahl_aufeinanderfolgender_plaetze_1
WHERE    anzahl = 8
ORDER BY anzahl, land, sportart, wettkampfart, geschlecht, platz_min
;

--
-- Variante 2 (GROUB BY, COUNT und HAVING)
--
DROP VIEW IF EXISTS anzahl_aufeinanderfolgender_plaetze_2;

CREATE VIEW
  anzahl_aufeinanderfolgender_plaetze_2
  (land, sportart, wettkampfart, geschlecht, platz_min, platz_max, anzahl)
AS
SELECT   st1.land, w.sportart, w.wettkampfart, w.geschlecht,
         st1.platz AS platz_min, st2.platz AS platz_max, 
         st2.platz - st1.platz + 1 AS anzahl  -- effizienter als COUNT(*)
FROM          st st1
         JOIN st st2      ON (    st2.w_id  = st1.w_id
                              AND st2.land  = st1.land
                              AND st2.platz > st1.platz
                             )
         JOIN st st3      ON (    st3.w_id  = st1.w_id
                              AND st3.land  = st1.land
                              AND st3.platz BETWEEN st1.platz AND st2.platz
                             )
         JOIN wettkampf w ON (st1.w_id = w.id)
GROUP BY st1.land, w.sportart, w.wettkampfart, w.geschlecht, st1.platz, st2.platz
HAVING   st2.platz = st1.platz+COUNT(*)-1 
;

-- Test-Anfragen
SELECT   *
FROM     anzahl_aufeinanderfolgender_plaetze_2
WHERE    anzahl = 5
ORDER BY anzahl, land, sportart, wettkampfart, geschlecht, platz_min
;
SELECT   *
FROM     anzahl_aufeinanderfolgender_plaetze_2
WHERE    anzahl = 8
ORDER BY anzahl, land, sportart, wettkampfart, geschlecht, platz_min
;

--
-- Variante 3 (LATERAL JOIN)
--
DROP VIEW IF EXISTS anzahl_aufeinanderfolgender_plaetze_3;

CREATE VIEW
  anzahl_aufeinanderfolgender_plaetze_3
  (land, sportart, wettkampfart, geschlecht, platz_min, platz_max, anzahl)
AS
SELECT st1.land, w.sportart, w.wettkampfart, w.geschlecht,
       st1.platz AS platz_min, st2.platz AS platz_max, 
       st2.platz - st1.platz + 1 AS anzahl -- effizienter als land_anzahl.anzahl
FROM        st st1
       JOIN st st2      ON (    st2.w_id  = st1.w_id
                            AND st2.land  = st1.land
                            AND st2.platz > st1.platz
                           )
       JOIN wettkampf w ON (st1.w_id = w.id),
       LATERAL
         (SELECT COUNT(*)
          FROM   st st3
          WHERE      st3.w_id = st1.w_id
                 AND st3.land = st1.land
                 AND st3.platz BETWEEN st1.platz AND st2.platz
         ) land_anzahl(anzahl)
WHERE st2.platz = st1.platz+anzahl-1
;

-- Test-Anfrage
SELECT   *
FROM     anzahl_aufeinanderfolgender_plaetze_3
WHERE    anzahl = 5
ORDER BY anzahl, land, sportart, wettkampfart, geschlecht, platz_min
;
SELECT   *
FROM     anzahl_aufeinanderfolgender_plaetze_3
WHERE    anzahl = 8
ORDER BY anzahl, land, sportart, wettkampfart, geschlecht, platz_min
;