# Übung 3 Aufgabe 5 - Simon Stadlinger, Julian Baumann
#### a)

SELECT n_name, l_year, SUM(volume) as profit FROM (
    SELECT n_name, EXTRACT(YEAR FROM o_orderdate) as l_year, (l_extendedprice*(1-
    l_discount)) - (ps_supplycost * l_quantity) as volume
    FROM nation, orders, lineitem, supplier, part, partsupp
    WHERE s_suppkey = l_suppkey
    AND o_orderkey = l_orderkey
    AND n_nationkey = s_nationkey
    AND l_partkey = ps_partkey
    AND l_suppkey = ps_suppkey
    AND p_partkey = ps_partkey
    AND p_name LIKE '%chocolate%'
    ) as chocSupp
GROUP BY n_name, l_year
ORDER BY n_name ASC, l_year DESC;

#### b)

WITH parts_germany AS (
    SELECT p_partkey, p_name, ps_availqty
    FROM part, partsupp, supplier, nation
    WHERE p_partkey = ps_partkey
    AND ps_availqty > 0
    AND ps_suppkey = s_suppkey
    AND s_nationkey = n_nationkey
    AND n_name = 'GERMANY'
)
SELECT p_partkey, p_name, SUM(ps_availqty) AS quantity
FROM parts_germany
GROUP BY p_partkey, p_name
HAVING SUM(ps_availqty) > (SELECT SUM(ps_availqty) FROM parts_germany)*0.00001
ORDER BY quantity DESC;