USE northwind;

CREATE TEMPORARY TABLE temp.Z1 
SELECT Z.KodZakaznika, COUNT(*) 
FROM Zakaznici Z, Objednavky O 
WHERE O.KOdZakaznika=Z.KodZakaznika 
AND O.DatumObjednavky>="1998-04-01" 
GROUP BY Z.KodZakaznika 
ORDER BY 2 DESC 
LIMIT 3;

CREATE TEMPORARY TABLE temp.O1
SELECT CisloObjednavky 
FROM Objednavky O JOIN temp.Z1 on O.KodZakaznika=Z1.KodZakaznika 
WHERE O.DatumObjednavky>="1998-04-01";

CREATE TEMPORARY TABLE temp.V1 
SELECT CisloVyrobku, SUM(VyslednaCena) AS trzbaOdNejvetsich 
FROM RozpisObjednavek R JOIN temp.O1 on R.CisloObjednavky=O1.CisloObjednavky 
GROUP BY CisloVyrobku;

CREATE TEMPORARY TABLE temp.V2 
SELECT CisloVyrobku, SUM(VyslednaCena) AS trzbaCelkem 
FROM RozpisObjednavek R inner JOIN Objednavky O on R.CisloObjednavky=O.CisloObjednavky 
WHERE DatumObjednavky>="1998-04-01" 
GROUP BY CisloVyrobku;

SELECT NazevVyrobku, trzbaCelkem, trzbaOdNejvetsich, TRUNCATE(100*trzbaOdNejvetsich/trzbaCelkem,1) AS procent
FROM Vyrobky V LEFT JOIN temp.V2 on V.CisloVyrobku=temp.V2.Cislovyrobku LEFT JOIN temp.V1 on temp.V2.CisloVyrobku=temp.V1.CisloVyrobku 
ORDER BY 4 DESC, 2 ASC;