-- Przykladowe rozwiazania dla zestawu B

-- 1. Posortuj klientow wedlug lacznej kwoty sprzedazy (wypisz imie, nazwisko i kwote). 
SELECT imie, nazwisko, NVL(SUM(kwota),0) obroty 
FROM klient left join transakcja on sprzedajacy=id 
GROUP BY id, imie, nazwisko 
ORDER BY obroty DESC;

--2. Wypisz id klientow, ktorzy kupili od wiecej niz 5 osob. 
SELECT kupujacy, COUNT(distinct sprzedajacy) liczba 
FROM transakcja 
GROUP BY transakcja.kupujacy
HAVING COUNT(distinct sprzedajacy) > 5;

-- 3. Dla kazdego klienta (id) podaj srednia kwote zakupow; jesli jeszcze nic nie kupil, to podaj 0. 

(SELECT kupujacy, avg(kwota) 
 FROM  transakcja 
 GROUP BY kupujacy)
union
(SELECT id,0 
 FROM klient 
 WHERE id not in (SELECT kupujacy FROM transakcja));

-- krocej w Oraclu: bez union, ale z nvl; sortowanie dla przejrzystosci

SELECT id, NVL(AVG(kwota),0) srednia_zakupow
FROM klient LEFT JOIN transakcja ON id=kupujacy
GROUP BY id
ORDER BY NVL(AVG(kwota),0) DESC;

-- 4. Wypisz transakcje (caly wiersz) miedzy osobami, ktore wykonaly mniej niz 3 transakcji.
-- Zeby zapytanie zwrocilo cos ciekawego na przykladowych danych, trzeba zmienic 3 na 5. 
WITH malo_aktywni AS 
   (SELECT id FROM klient LEFT JOIN transakcja ON id = kupujacy OR id = sprzedajacy 
    GROUP BY id HAVING COUNT(*) < 3)
SELECT *
FROM transakcja 
WHERE sprzedajacy in (SELECT * FROM malo_aktywni) AND kupujacy in (SELECT * FROM malo_aktywni);

-- inaczej, choc podobnie
SELECT A.kupujacy, A.sprzedajacy 
FROM transakcja A
WHERE (SELECT count(*) FROM transakcja B WHERE A.kupujacy = B.kupujacy OR A.kupujacy = B.sprzedajacy) < 3 
  AND (SELECT count(*) FROM transakcja B WHERE A.sprzedajacy = B.kupujacy OR A.sprzedajacy = B.sprzedajacy) < 3;


-- 5. Wypisz id klientow, ktorzy byli uczestnikami choc jednej transakcji. 
SELECT id 
FROM klient 
WHERE exists 
  (SELECT kupujacy,sprzedajacy 
   FROM transakcja 
   WHERE kupujacy = id OR sprzedajacy = id);

-- krocej 
SELECT kupujacy uczestnik FROM transakcja UNION SELECT sprzedajacy uczestnik FROM transakcja;


-- 6. Wypisz id kupcow, ktorzy wydali laczna kwote > 10000 w < 5 transakcjach. 
SELECT kupujacy
FROM transakcja a
WHERE 
  (SELECT COUNT(*) 
   FROM transakcja b 
   WHERE a.kupujacy=b.kupujacy AND 
         (a.kwota<b.kwota OR (a.kwota=b.kwota AND a.sprzedajacy < b.sprzedajacy) 
                          OR (a.kwota=b.kwota AND a.sprzedajacy = b.sprzedajacy AND a.data<=b.data))) < 5
GROUP BY a.kupujacy 
HAVING SUM(a.kwota)>=10000; 

-- w MySQL mozna tak:
SELECT id, SUM(kwota) 
FROM 
     (SELECT kwota, kupujacy AS id 
      FROM transakcja 
      WHERE kupujacy = id 
      ORDER BY kwota DESC 
      limit 5) 
GROUP BY id 
HAVING SUM(kwota) >= 10000;

--7. Wypisz id klientow, ktorzy zawarli  transakcje z kazdym innym klientem.

SELECT kl1.id 
FROM klient kl1 
WHERE NOT EXISTS
  (SELECT * 
   FROM klient kl2 
   WHERE kl1.id != kl2.id AND NOT EXISTS 
     (SELECT * 
      FROM transakcja 
      WHERE (sprzedajacy=kl1.id AND kupujacy=kl2.id) 
         OR (sprzedajacy=kl2.id AND kupujacy=kl1.id)));

-- bardzo eleganckie rozwiazanie p. Przemyslawa Kuczynskiego
SELECT id1 klient 
FROM
        (SELECT sprzedajacy id1, kupujacy id2 FROM transakcja
        UNION
        SELECT kupujacy id1, sprzedajacy id2 FROM transakcja)
GROUP BY id1 
HAVING COUNT(id2) = ((SELECT COUNT(*) FROM klient) - 1);


-- 8. Wypisz id klientow, ktorzy mieszczą sie w 40% najaktywniejszych sprzedawcow (w rankingu wg. lacznej kwoty sprzedazy). 


-- rozwiazanie "podrecznikowe"

WITH got_tenth AS
     (SELECT id, SUM(kwota) AS suma, ROW_NUMBER () OVER (ORDER BY SUM(kwota) DESC) AS r_num, COUNT (*) OVER () AS n_rows 
     FROM klient, transakcja 
     WHERE id = sprzedajacy 
     GROUP BY id)
SELECT id, suma
FROM got_tenth
WHERE r_num <= 0.4 * n_rows
ORDER BY suma DESC;


-- rozwiazanie "na piechote"

WITH sprzed AS
     (SELECT sprzedajacy id, SUM(kwota) suma FROM transakcja GROUP BY sprzedajacy)
SELECT a.id, a.suma
FROM sprzed a LEFT JOIN sprzed b on b.suma > a.suma or (b.suma = a.suma and b.id >= a.id)
GROUP BY a.id, a.suma
HAVING COUNT(*) <= 0.4 * (SELECT COUNT(*) FROM klient);
