Kategoria: Poziom średniozaawansowany

Zadanie 25.

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ń?

s25a

 

 

 

b) podsumowanie sprzedaży według klienta i sprzedawcy (rollup)

s25b

 

 

 

 

 

 

 

c) wyznacz sprzedawców, którzy odpowiadają (łącznie) za ok. 70% sprzedaży.

s25c

 

 

 

d) wyznacz średnią wartość sprzedaży dla każdego sprzedawcy w danym roku oraz ilość obsłużonych klientów

 

s25d

 

 

 

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

Rozwiązanie

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;

[collapse]

Zadanie 24.

Korzystając z widoku utworzonego w zadaniu 23, podsumujmy sprzedaż według produktów.

a) które produkty sprzedawały się najlepiej – ranking top 10

s24a

 

 

 

 

b) który produkt sprzedawał się najlepiej w konkretnych latach – ranking top 5 produktów na dany rok

s24b

 

 

 

 

 

c) który produkt sprzedawał się najlepiej w każdej kategorii?

s24c

 

 

 

 

 

d) który produkt sprzedawał się najlepiej w pierwszych trzech miesiącach lat 2006-2007? Ranking top 3 produktów.

s24d

 

 

 

 

Schemat: OE, Tabela: Product_Information oraz widok utworzony w zadaniu 23, Kolumny: product_id, quantity, ord_year, ord_month, category_id, Wynik: a) 10 rows b) 20 rows c) 17 rows d) 15 rows

Rozwiązanie

a) SELECT product_id, SUM(quantity) AS quantity, dense_rank() over(order by SUM(quantity) DESC) AS rank
FROM my_order_view
GROUP BY product_id
ORDER BY quantity DESC
FETCH FIRST 10 rows WITH ties;

b) WITH Subquery AS (
SELECT ord_year, product_id, SUM(quantity) AS quantity,
dense_rank() over(partition by ord_year order by SUM(quantity) DESC) AS rank
FROM my_order_view
GROUP BY product_id, ord_year
)

SELECT * FROM Subquery
WHERE rank <= 5
ORDER BY ord_year, rank;

c) WITH Subquery AS (
SELECT pi.category_id, product_id, SUM(quantity) AS quantity,
dense_rank() over(partition by pi.category_id order by SUM(quantity) DESC) AS rank
FROM my_order_view inner join product_information pi using(product_id)
GROUP BY pi.category_id, product_id
)

SELECT category_id, product_id, quantity
FROM Subquery
WHERE rank = 1;

d) WITH Subquery AS (
SELECT ord_year, ord_month, product_id, SUM(quantity) AS quantity,
dense_rank() over(partition by ord_year, ord_month order by SUM(quantity) DESC) AS rank
FROM my_order_view
WHERE ord_month in(1,2,3) and ord_year in(2006, 2007)
GROUP BY ord_year, ord_month, product_id
)

SELECT * FROM Subquery
WHERE rank <= 3;

[collapse]

Zadanie 23.

Stwórzmy widok, zawierający dane dot. sprzedaży, który posłuży nam w kolejnych zadaniach jako źródło danych do analizy. Widok powinien zawierać takie informacje, jak: nr zamówienia, nr klienta, rok złożenia zamówienia, miesiąc złożenia zamówienia, kwota zamówienia, id produktu, ilość produktu, id sprzedającego.

Schemat: OE, Tabele: Orders, Order_Items, Kolumny: order_id, order_date, customer_id, order_total, sales_rep_id, product_id, quantity, Wynik: 665 rows

s23

 

 

Rozwiązanie

CREATE OR REPLACE VIEW My_Order_View
AS
SELECT order_id, EXTRACT(YEAR from o.order_date) as ord_year, EXTRACT(MONTH from o.order_date) as ord_month,
oi.product_id, oi.quantity, o.customer_id, o.sales_rep_id, o.order_total
FROM orders o
INNER JOIN order_items oi USING(order_id);

SELECT * FROM My_Order_View
ORDER BY ord_year, ord_month;

 

[collapse]

Zadanie 22.

Oblicz łączną sumę tych zamówień, których wartość wynosi powyżej 35000. Zadanie rozwiąż na kilka sposobów.

Schemat: OE, Tabela: Orders, Kolumny: order_total, Wynik: 1

s22

 

Rozwiązanie

1. Wersja najprostsza:

SELECT SUM(order_total) AS wynik
FROM orders
WHERE order_total > 35000;

2. Wersja z CASE:

SELECT SUM(
CASE
WHEN order_total < 35000
THEN 0
ELSE order_total
END) AS wynik
FROM orders;

3. Z subquery factoring:

WITH subquery AS (
SELECT order_total
FROM orders
WHERE order_total > 35000
)
SELECT SUM(order_total) AS wynik FROM subquery;

4. Z podzapytaniem w klauzuli FROM:

SELECT SUM(order_total) AS wynik
FROM
( SELECT order_total
FROM orders
WHERE order_total > 35000
) Tab;

[collapse]

Zadanie 21.

Stwórz podsumowanie sprzedażowe – który ze sprzedawców jakich klientów obsłużył, na jaką kwotę ci klienci złożyli zamówienia, oraz jaką łączną wartość zamówień obsłużył sprzedawca.

Schemat: OE, Tabela: Orders, Kolumny: sales_rep_id, customer_id, order_total, Wynik: 76

s21

 

 

 

Rozwiązanie

SELECT NVL(TO_CHAR(sales_rep_id), 'total') AS "sales rep. id", NVL(TO_CHAR(customer_id), 'Sales Rep. total') AS "customer id", SUM(order_total) AS "Sum"
FROM orders
WHERE sales_rep_id IS NOT NULL
GROUP BY rollup(sales_rep_id, customer_id)
ORDER BY sales_rep_id;

[collapse]

Zadanie 20.

Znajdź zamówienia złożone przez klientów, których credit_limit jest większy niż 1000. Zadanie rozwiąż na dwa sposoby.

Schemat: OE, Tabele: Orders, Customers, Kolumny: order_id, customer_id, Wynik: 7

s20

 

 

 

 

 

Rozwiązanie

1. Łączenie tabel:

SELECT o.order_id, customer_id
FROM orders o
INNER JOIN customers c USING (customer_id)
WHERE c.credit_limit > 1000;

2. Podzapytania:

SELECT order_id, customer_id
FROM orders
WHERE customer_id IN
( SELECT customer_id FROM customers WHERE credit_limit > 1000
);

[collapse]

Zadanie 19.

Korzystając z tabeli Orders, utwórz zapytanie, które zwróci ID klienta, numer zamówienia, wartość zamówienia, procentowy udział zamówienia we wszystkich zamówieniach klienta oraz łączną wartość zamówień klienta.

Schemat: OE, Tabela: Orders, Kolumny: customer_id, order_id, order_total, Wynik: 105

s19

 

 

 

Rozwiązanie

SELECT customer_id, order_id, order_total,
CAST(100* order_total / SUM(order_total) over(partition BY customer_id) AS NUMBER(5,2)) AS order_pct,
SUM(order_total) over(partition BY customer_id) AS all_orders
FROM orders
ORDER BY customer_id, order_id;

[collapse]

Zadanie 18.

Znajdź produkty, które nie były zamówione ani razu. Zadanie rozwiąż więcej niż jednym sposobem.

Schemat: OE, Tabele: Order_Items, Product_Information, Kolumny: product_id, order_id, Wynik: 103

s18

 

 

 

 

 

 

Rozwiązanie

1. Z użyciem MINUS:

SELECT product_id FROM product_information
MINUS
SELECT product_id FROM order_items ORDER BY product_id;

2. Z wykorzystaniem złączenia OUTER JOIN:
SELECT product_id
FROM product_information
FULL OUTER JOIN order_items oi USING(product_id)
WHERE oi.order_id IS NULL
ORDER BY product_id;

3. Z podzapytaniem:
SELECT product_id
FROM product_information
WHERE product_id NOT IN
( SELECT DISTINCT product_id FROM order_items
)
ORDER BY product_id;

[collapse]

Zadanie 17.

Wybierz product_id i pokaż, ile razy był zamówiony (w ilu zamówieniach się znalazł – ilościowo oraz procentowo).

Schemat: OE, Tabela: Order_Items, Kolumny: product_id, order_id, Wynik: 185 rows

s17

 

 

 

Rozwiązanie


SELECT product_id, COUNT(order_id) AS ilosc_zamowien, (SELECT COUNT(*) FROM orders) AS total,
CAST(100* COUNT(order_id) / (SELECT COUNT(*) FROM orders) AS NUMBER(5,2)) AS w_ilu_prct_zamowien
FROM order_items
GROUP BY product_id
ORDER BY ilosc_zamowien DESC;

[collapse]

Zadanie 16.

Podlicz wartość zamówień każdego klienta w podziale na te złożone bezpośrednio (direct) lub online.

Schemat: OE, Tabela: Orders, Kolumny: customer_id, order_mode, order_total, Wynik: 62 rows

s16

 

 

 

 

 

 

Rozwiązanie

1. Z podzapytaniami:

WITH subquery AS (
SELECT customer_id,
CASE order_mode
WHEN 'direct' THEN SUM(order_total)
END AS direct,
CASE order_mode
WHEN 'online' THEN SUM(order_total)
END AS onlines
FROM orders
GROUP BY customer_id, order_mode
)

SELECT customer_id, NVL(direct, 0) as direct, NVL(onlines, 0) as "ONLINE"
FROM subquery
ORDER BY customer_id;

2. Bez podzapytań:

SELECT customer_id,
SUM(
CASE order_mode
WHEN 'direct'
THEN order_total
ELSE 0
END)AS direct,
SUM(
CASE order_mode
WHEN 'online'
THEN order_total
ELSE 0
END)AS "ONLINE"
FROM ORDERS
GROUP BY customer_id, order_mode
ORDER BY customer_id;

[collapse]
« Starsze wpsiy