/* ZAD 1*/
SELECT p.PART#, p.NAME, COUNT(od.ORDER#) AS ORDERS, '$' || ' ' || COALESCE(SUM(od.quoted_price*od.NUM_ORDERED)/COUNT(od.ORDER#),0) AS PRICE,
DENSE_RANK() OVER (ORDER BY SUM(od.quoted_price*od.NUM_ORDERED) DESC NULLS LAST) AS RANKING
FROM PART p
LEFT JOIN ORDER_DETAILS od ON p.PART# = od.PART#
GROUP BY p.PART#, p.NAME;

/* ZAD 2 */
SELECT C.CITY AS "CITY", P.PART# || ': ' || P.NAME AS "PART", SUM(OD.NUM_ORDERED) AS "TOTAL"
FROM CUSTOMER C
JOIN T_ORDERS O ON C.CUSID = O.CUSID
JOIN ORDER_DETAILS OD ON OD.ORDER# = O.ORDER#
JOIN PART P ON OD.PART# = P.PART#
GROUP BY GROUPING SETS ((C.CITY, P.PART#, P.NAME),(C.CITY),());

/* ZAD 3 */
SELECT PART, CITY, TOTAL
FROM (
SELECT C.CITY AS "CITY", P.PART# || ': ' || P.NAME AS "PART", SUM(OD.NUM_ORDERED) AS "TOTAL",
DENSE_RANK() OVER (PARTITION BY P.PART# ORDER BY SUM(OD.NUM_ORDERED) DESC) AS RANK
FROM CUSTOMER C
JOIN T_ORDERS O ON C.CUSID = O.CUSID
JOIN ORDER_DETAILS OD ON OD.ORDER# = O.ORDER#
JOIN PART P ON OD.PART# = P.PART#
GROUP BY GROUPING SETS ((C.CITY, P.PART#, P.NAME))
ORDER BY P.NAME
) WHERE RANK = 1;

/* ZAD 4 */

SELECT (c.CUSID || ' ' || c.CUS_NAME) AS CUSTOMER, COUNT(DISTINCT(to1.ORDER#)) AS NUM_ORDS,
COUNT(od.PART#) AS IND_PARTS, '$' || COALESCE(SUM(od.num_ordered*od.QUOTED_PRICE),0) AS TOTAL
FROM CUSTOMER c
LEFT JOIN T_ORDERS to1 ON to1.CUSID = c.CUSID
LEFT JOIN ORDER_DETAILS od ON od.ORDER# = to1.ORDER#
GROUP BY c.CUSID || ' ' || c.CUS_NAME
ORDER BY SUM(od.num_ordered*od.QUOTED_PRICE) DESC NULLS LAST;

/* ZAD 5 */
SELECT NAME, TOTAL, INDICATOR
FROM (
SELECT r.REPID || ' ' || r.FIRST_NAME || ' ' || r.LAST_NAME AS NAME, '$' || SUM(od.num_ordered*od.QUOTED_PRICE) AS TOTAL,
'TOTAL VALUE OF SALES' AS INDICATOR, DENSE_RANK() OVER (ORDER BY SUM(od.num_ordered*od.QUOTED_PRICE) DESC) AS RANK
FROM T_ORDERS to1
JOIN REP r ON r.REPID = to1.REPID
JOIN ORDER_DETAILS od ON to1.ORDER# = od.ORDER#
GROUP BY r.REPID || ' ' || r.FIRST_NAME || ' ' || r.LAST_NAME
) WHERE RANK = 1
UNION
SELECT NAME, TOTAL, INDICATOR
FROM (
SELECT r.REPID || ' ' || r.FIRST_NAME || ' ' || r.LAST_NAME AS NAME, CAST(SUM(od.num_ordered) AS VARCHAR2(3)) AS TOTAL,
'QUANTITY OF ITEMS SOLD' AS INDICATOR, DENSE_RANK() OVER (ORDER BY SUM(od.num_ordered) DESC) AS RANK1
FROM T_ORDERS to1
JOIN REP r ON r.REPID = to1.REPID
JOIN ORDER_DETAILS od ON to1.ORDER# = od.ORDER#
GROUP BY r.REPID || ' ' || r.FIRST_NAME || ' ' || r.LAST_NAME
) WHERE RANK1 = 1
UNION
SELECT NAME, TOTAL, INDICATOR
FROM (
SELECT r.REPID || ' ' || r.FIRST_NAME || ' ' || r.LAST_NAME AS NAME, CAST(COUNT(to1.ORDER#) AS VARCHAR(3)) AS TOTAL,
'TOTAL NUM OF ORDERS' AS INDICATOR, DENSE_RANK() OVER (ORDER BY COUNT(to1.ORDER#) DESC) AS RANK2
FROM T_ORDERS to1
JOIN REP r ON r.REPID = to1.REPID
GROUP BY r.REPID || ' ' || r.FIRST_NAME || ' ' || r.LAST_NAME
) WHERE RANK2 = 1;

/*ZAD 6 */
SELECT CUSTOMER, RANK1, RANK2
FROM (
    SELECT C.CUSID || ' ' || C.CUS_NAME AS "CUSTOMER", RANK() OVER (ORDER BY COUNT(DISTINCT(O.ORDER#)) DESC) AS RANK1,
    DENSE_RANK() OVER (ORDER BY SUM(OD.NUM_ORDERED * OD.QUOTED_PRICE)/COUNT(DISTINCT(OD.ORDER#)) DESC) AS RANK2
    FROM CUSTOMER C
    JOIN T_ORDERS O ON C.CUSID = O.CUSID
    JOIN ORDER_DETAILS OD ON O.ORDER# = OD.ORDER#
    GROUP BY C.CUSID, C.CUS_NAME
)
WHERE (RANK1 = 1 OR RANK1 = 2) OR (RANK2 = 1 OR RANK2 =2);