Tagi: SUM

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 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 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 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]

Zadanie 4.

Wcielmy w życie zasadę Pareto (zasada 80/20). Mówi ona, że tylko ok. 20% klientów firmy odpowiada za ok. 80% jej przychodów. Sprawdźmy, czy podobna relacja zachodzi i tu.

W tym celu będziemy potrzebować: ID klienta, łącznej wartości zamówień klienta, udziału procentowego tych zamówień w stosunku do wszystkich zamówień, oraz podliczenia punktów procentowych.

Schamat: OE, Tabela: Customers, Kolumny: customer_id, order_total, Wynik: 47 rows

s4

 

 

 

 

Rozwiązanie

WITH Subquery AS
( SELECT DISTINCT customer_id,
SUM(order_total) OVER(partition BY customer_id) AS Customer_Total,
SUM(order_total) OVER() AS Total,
CAST(100* SUM(order_total) OVER(partition BY customer_id) / SUM(order_total) OVER() AS NUMBER(5,2)) AS Customer_Percent
FROM orders
)
SELECT customer_id, customer_total, customer_percent,
SUM(customer_percent) OVER(order by customer_percent DESC) AS Sum_Pct
FROM Subquery
ORDER BY customer_total DESC;

[collapse]

Zadanie 9.

Stwórz raport sprzedażowy – policz wartość zamówień w każdym miesiącu każdego roku. Kolumnę z wartościami zamówień sformatuj według wzoru (screen).

Schemat: OE, Tabela: Orders, Kolumny: order_date, order_total, Wynik: 25 rows

p9

 

 

 

 

 

Rozwiązanie

SELECT to_char(order_date, 'YYYY') as "Year", to_char(order_date, 'MM') as "Month", to_char(SUM(order_total), '999,999.99') as "Total"
FROM orders
GROUP BY to_char(order_date, 'YYYY'), to_char(order_date, 'MM')
ORDER BY "Year", "Month";

[collapse]

Zadanie 7.

Policz, ile zamówień i na jaką łączną wartość złożył każdy z klientów w 2006 roku.

Schemat: OE, Tabela: Orders, Kolumny: customer_id, order_id, order_total, order_date, Wynik: 13 rows

p7

 

 

 

Rozwiązanie

SELECT customer_id, COUNT(order_id) as Zamowienia, SUM(order_total) as Wartosc
FROM Orders
WHERE EXTRACT(YEAR FROM order_date) = '2006'
GROUP BY customer_id;

[collapse]
« Starsze wpsiy