Korzystając z widoku z zadania 23, stwórzmy podsumowanie sprzedażowe z punktu widzenia sprzedawców i klientów:
- a) Który klient złożył najwięcej zamówień, a który miał największą wartość zamówień?
- b) podsumowanie sprzedaży według klienta i sprzedawcy (rollup)
- c) wyznacz sprzedawców, którzy odpowiadają (łącznie) za ok. 70% sprzedaży.
- d) wyznacz średnią wartość sprzedaży dla każdego sprzedawcy w danym roku oraz ilość obsłużonych klientów
Schemat: OE, Tabela: my_order_view (widok z zadania 23) , Kolumny: customer_id, order_id, order_total, sales_rep_id, ord_year, Wynik: a) 2 rows b) 112 rows c) 4 rows d) 20 rows
[spoiler title=’Rozwiązanie’ style=’default’ collapse_link=’true’]- a) WITH cte1 AS
(SELECT customer_id,
COUNT(order_id) AS orders
FROM my_order_view
GROUP BY customer_id
ORDER BY COUNT(order_id) DESC
FETCH FIRST row only
),
cte2 AS
(SELECT customer_id,
SUM(order_total) AS total
FROM my_order_view
GROUP BY customer_id
ORDER BY COUNT(order_total) DESC
FETCH FIRST row only
)
SELECT 'Max orders:’ AS description, customer_id, orders AS total FROM cte1
UNION
SELECT 'Max value:’, customer_id, total FROM cte2;
- b) SELECT NVL(TO_CHAR(customer_id), 'total’) AS customer_id,
NVL(TO_CHAR(sales_rep_id), 'total’) AS sales_rep_id,
SUM(order_total) AS order_total
FROM my_order_view
WHERE sales_rep_id IS NOT NULL
GROUP BY cube(customer_id, sales_rep_id)
ORDER BY customer_id, sales_rep_id;
- c) WITH cte AS
( SELECT DISTINCT sales_rep_id,
SUM(order_total) over(partition BY sales_rep_id) AS sales_rep_total,
SUM(order_total) over() AS total,
CAST(100* SUM(order_total) over(partition BY sales_rep_id) / SUM(order_total) over() AS NUMBER(5,2)) AS sales_rep_pct
FROM my_order_view
WHERE sales_rep_id IS NOT NULL
),
cte2 AS
(SELECT sales_rep_id,
sales_rep_total,
sales_rep_pct,
SUM(sales_rep_pct) over(order by sales_rep_pct DESC) AS total_pct
FROM cte
)
SELECT * FROM cte2 WHERE total_pct <= 70 ORDER BY sales_rep_pct DESC;
- d) SELECT ord_year, sales_rep_id, TO_CHAR(AVG(order_total), '99999D99′) AS orders_total,
COUNT(customer_id) AS customers
FROM my_order_view
WHERE sales_rep_id IS NOT NULL
GROUP BY sales_rep_id, ord_year
ORDER BY ord_year, sales_rep_id;
[/spoiler]