Tagi: AVG

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 9.

Znajdź produkty, których cena jest wyższa niż średnia cena z ich kategorii.

Schemat: OE, Tabela: Product_Information, Kolumny: product_id, product_name, category_id, list_price, Wynik: 91 rows

s9

 

 

 

Rozwiązanie

SELECT pi1.product_id, pi1.product_name, pi1.category_id, pi1.list_price
FROM product_information pi1
WHERE pi1.list_price > (
SELECT AVG(pi2.list_price)
FROM product_information pi2
WHERE pi1.category_id = pi2.category_id
GROUP BY pi2.category_id)
ORDER BY pi1.category_id, pi1.list_price;

[collapse]

Zadanie 24.

Dla zamówień o numerach 2355, 2413, 2377 i 2361 policz:

a) ile produktów zawiera każde z nich oraz podaj średnią cenę produktu

b) podaj kraj, z którego zostało złożone zamówienie; ile zamówień ma na koncie klient, który je złożył?

Schemat: OE, Tabele: Orders, Customers, Order_Items, Kolumny: order_id, product_id, unit_price, nls_territory, Wynik: 4 rows

Rozwiązanie

a) SELECT order_id, COUNT(product_id) as Products, to_char(AVG(unit_price), '999D99')as Avg_Price
FROM orders o INNER JOIN order_items oi USING(order_id)
WHERE order_id IN(2355, 2413, 2377, 2361)
GROUP BY order_id;

b) SELECT customer_id, MIN(c.nls_territory) as Country, COUNT(o.order_id) as Orders
FROM orders o INNER JOIN customers c USING(customer_id)
WHERE o.order_id IN(2355, 2413, 2377, 2361)
GROUP BY customer_id;

[collapse]

Zadanie 22.

Dla każdego klienta, który złożył zamówienie, określ średnią wartość zamówienia. Ponumeruj klientów wg najwyższej średniej wartości zamówienia.

Schemat: OE, Tabele: Customers, Orders, Kolumny: customer_id, order_total, Wynik: 47 rows

p22

 

 

 

 

 

Rozwiązanie

SELECT customer_id, AVG(o.order_total) as "Avg Order Value", ROW_NUMBER() OVER(ORDER BY AVG(o.order_total) DESC) as Num
FROM orders o INNER JOIN customers c
USING(customer_id)
GROUP BY customer_id
ORDER BY Num;

[collapse]

Zadanie 12.

Podlicz, ile jest produktów w każdej kategorii oraz policz średnią cenę produktu w kategorii.

Schemat: OE, Tabela: Product-Information, Kolumny: category_id, product_id, list_price, Wynik: 17 rows

p12

 

 

 

 

 

Rozwiązanie

SELECT category_id as "Category", COUNT(product_id) as "Count", to_char(AVG(list_price), '9999D99') as "Avg price"
FROM product_information
GROUP BY category_id
ORDER BY category_id;

[collapse]

Zadanie 1.

a) Policz średnią długość stażu pracy dla poszczególnych stanowisk.

b) W którym departamencie staż pracy jest najniższy, a w którym najwyższy? (średnie)

c) Znajdź pracowników, którzy mają więcej niż jeden okres zatrudnienia.

Schemat: HR, Tabela: Job_History, Kolumny: job_id, employee_id, start_date, end_date, department_id, Wynik: a) 8 rows b) 6 rows c) 3 rows

Rozwiązanie

a) SELECT job_id, AVG(end_date - start_date) as AVGHireDays
FROM Job_History
GROUP BY job_id
ORDER BY AVGHireDays DESC;

b) SELECT department_id, AVG(end_date - start_date) as AVGHireDays
FROM Job_History
GROUP BY department_id
ORDER BY AVGHireDays DESC;

c) SELECT employee_id, COUNT(*)
FROM Job_History
GROUP BY employee_id
HAVING COUNT (*) > 1;

[collapse]

Zadanie 9.

Znajdź numery tych zamówień, których wartość jest co najmniej trzy razy większa niż średnia wartość zamówienia.

Użyj widoku Order Subtotals (odwołujesz się do niego tak samo, jak do tabeli).

Baza: Northwind, Tabela (widok): dbo.Order Subtotals, Kolumny: OrderID, Subtotal, Wynik: 47 rows

s9

Rozwiązanie

SELECT os1.OrderID, os1.Subtotal
FROM dbo.[Order Subtotals] as os1
WHERE os1.Subtotal >= 3* ( SELECT AVG(os2.Subtotal) FROM dbo.[Order Subtotals] as os2 )
ORDER BY os1.OrderID;

[collapse]

Zadanie 6.

Dla zamówień o numerach 10250, 10657, 10710 i 10901 policz, ile produktów zawiera każde z nich, oblicz średnią cenę produktu i podaj kraj, do którego zostało dostarczone.

Baza: Northwind, Tabela: dbo.Order Details, dbo.Orders, Kolumny:  OrderID, UnitPrice, Quantity, ShipCountry, Wynik: 4 rows

10

Rozwiązanie

SELECT O.OrderID, COUNT(*) as Produkty, AVG(OD.UnitPrice*OD.Quantity) as Srednia, O.ShipCountry
FROM dbo.[Order Details] as OD
JOIN dbo.Orders as O on OD.OrderID = O.OrderID
GROUP BY O.OrderID, O.ShipCountry
HAVING O.OrderID IN(10250, 10657, 10710, 10901);

[collapse]