Każde zadanie wymaga nie tylko poprawnej składni, ale też świadomego doboru JOIN, GROUP BY, HAVING i podzapytań. Część z nich można rozwiązać na wiele sposobów — przez CTE, podzapytanie w FROM, a czasem funkcje okienkowe. Właśnie to sprawia, że są świetnym testem dla średniozaawansowanego analityka lub developera SQL.

Zadania SQL: poziom średni

Dataset i kontekst biznesowy

Pracujemy na prostym, realistycznym e-commerce: customers(id, name, email, country, registered_at), categories(id, name), products(id, name, category_id, price), orders(id, customer_id, order_date, status), order_items(order_id, product_id, quantity, unit_price), payments(id, order_id, amount, paid_at, method, status), shipments(id, order_id, shipped_at, delivered_at, status). Klucze: PK na id; FK: orders.customer_id → customers.id, products.category_id → categories.id, order_items.(order_id, product_id) → orders.id, products.id, payments.order_id → orders.id, shipments.order_id → orders.id.

Dane są typowe: kilkanaście tysięcy customers, setki products, setki tysięcy order_items. Zadania SQL poziom średni obejmują JOIN, GROUP BY, podzapytania i filtry biznesowe. Jeśli dopiero wchodzisz w ćwiczenia SQL, zajrzyj do Podstawy SQL: SELECT, WHERE, JOIN, GROUP BY.

SQL
Kopiuj
1
2
3
4
5
6
7
8
9
10
11
12
13
-- Szybka pomocnicza agregacja wartości zamówienia
-- (PostgreSQL i MySQL 8+)
WITH order_totals AS (
  SELECT oi.order_id, SUM(oi.quantity * oi.unit_price) AS order_total
  FROM order_items oi
  GROUP BY oi.order_id
)
SELECT o.id, c.name AS customer, o.order_date, ot.order_total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_totals ot ON ot.order_id = o.id
ORDER BY o.order_date DESC
LIMIT 5;

10–12 zadań obejmujących JOIN, GROUP BY i podzapytania

Poniżej znajdziesz zestaw ćwiczeń SQL dla poziomu średniozaawansowanego. Każde zadanie wskazuje na konkretny problem analityczny: agregacje, relacje wiele–do–wielu i typowe scenariusze biznesowe. Skup się na poprawnym doborze kluczy, filtrów i miejsc, gdzie GROUP BY oraz HAVING mają sens.

Zalecenie: do wyliczeń wartości zamówienia używaj SUM(quantity*unit_price) po order_items; do filtrów istnienia używaj EXISTS/NOT EXISTS; dla liczby różnych kategorii COUNT(DISTINCT ...). Jeśli potrzebujesz przypomnienia o CTE i podzapytaniach, zobacz Podzapytania i CTE w SQL.

SQL
Kopiuj
1
2
3
4
5
6
7
8
9
10
11
12
13
-- Zadania (opis):
-- 1) Dla każdego klienta: liczba zamówień, łączna wartość i tylko ci z >3 zamówieniami (JOIN + GROUP BY + HAVING).
-- 2) Top 5 produktów wg przychodu (SUM(qty*price)), zwróć id, name, revenue (JOIN + GROUP BY + ORDER BY LIMIT).
-- 3) Średnia wartość zamówienia per kraj (JOIN customers -> orders -> order_items, GROUP BY country).
-- 4) Zamówienia bez żadnej płatności (LEFT JOIN payments, WHERE payments.id IS NULL).
-- 5) Produkty, które nigdy nie zostały sprzedane (NOT EXISTS względem order_items).
-- 6) Najnowsze zamówienie per klient (podzapytanie korelowane albo CTE + MAX(order_date)).
-- 7) Klienci, którzy kupili w więcej niż 1 kategorii (COUNT(DISTINCT category_id) > 1).
-- 8) Udział kategorii w całkowitym przychodzie (procent = revenue_category / revenue_all).
-- 9) Zamówienia z co najmniej 3 pozycjami (COUNT(order_items) >= 3).
-- 10) Zamówienia opłacone (payments.status='paid'), ale niedostarczone (shipments.delivered_at IS NULL).
-- 11) Zamówienia z rozbieżnością: suma pozycji != suma płatności (tolerancja 0.01).
-- 12) Dla każdego kraju: klient z największą wartością pojedynczego zamówienia (subquery po max order_total).

Wyzwania z funkcjami okienkowymi

Ta sekcja celuje w sql średniozaawansowany z oknami analitycznymi. Użyj OVER(PARTITION BY ... ORDER BY ...) do rankingów, kumulacji i porównań w czasie. MySQL 8+ i PostgreSQL wspierają LAG/LEAD, ROW_NUMBER, RANK/DENSE_RANK oraz okna ramowe.

Trzy zadania: (A) skumulowany przychód klienta w kolejności dat zamówień; (B) drugie zamówienie klienta (ROW_NUMBER=2); (C) ranking produktów w ramach kategorii wg przychodu i wybór top 3. Jeśli potrzebujesz szerszego wprowadzenia, sprawdź Funkcje okienkowe w SQL: PostgreSQL i MySQL.

SQL
Kopiuj
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- A) Skumulowany przychód klienta po dacie zamówienia
WITH order_totals AS (
  SELECT o.id, o.customer_id, o.order_date,
         SUM(oi.quantity * oi.unit_price) AS order_total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.id, o.customer_id, o.order_date
)
SELECT c.id AS customer_id,
       o.order_date,
       order_total,
       SUM(order_total) OVER (
         PARTITION BY c.id ORDER BY o.order_date
       ) AS cum_revenue
FROM order_totals o
JOIN customers c ON c.id = o.customer_id;

-- B) Drugie zamówienie klienta
WITH ranked AS (
  SELECT o.id, o.customer_id, o.order_date,
         ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date) AS rn
  FROM orders o
)
SELECT * FROM ranked WHERE rn = 2;

-- C) Top 3 produkty w kategorii wg przychodu
WITH prod_rev AS (
  SELECT p.id AS product_id, p.category_id,
         SUM(oi.quantity * oi.unit_price) AS revenue
  FROM products p
  JOIN order_items oi ON oi.product_id = p.id
  GROUP BY p.id, p.category_id
),
ranked AS (
  SELECT product_id, category_id, revenue,
         DENSE_RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC) AS rnk
  FROM prod_rev
)
SELECT * FROM ranked WHERE rnk <= 3;

Zadania optymalizacyjne

Optymalizacja zapytań SQL obejmuje dobór indeksów, eliminację zbędnych skanów i redukcję transferu danych. Skup się na kolumnach łączących (FK), kolumnach w WHERE oraz porządku kolumn w indeksach złożonych zgodnie z selektywnością i predykatami zakresowymi. Mierz efekty przez EXPLAIN/EXPLAIN ANALYZE i kontroluj, by agregacje wykorzystywały mniejsze zbiory wejściowe.

Praktyka: najpierw plan, potem indeks. Indeks na (orders.customer_id, order_date) pomaga rankingom i filtracji per klient; na order_items(order_id, product_id) wspiera join i agregację; na payments(order_id, status) przyspieszy wyszukiwanie płatnych. Dalsze wskazówki w Optymalizacja zapytań SQL i analiza planów wykonania.

SQL
Kopiuj
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 1) Sprawdź plan drogiego zapytania
EXPLAIN
SELECT o.id
FROM orders o
LEFT JOIN payments p ON p.order_id = o.id AND p.status = 'paid'
WHERE p.id IS NULL
AND o.status = 'confirmed';

-- 2) Indeksy wspierające powyższe zapytanie
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_payments_order_status ON payments(order_id, status);

-- 3) Przychód produktu: ogranicz skan danych przez prefiltr
-- Anty-wzorzec: agregacja bez zawężenia
-- Poprawa: najpierw zawężenie po product_id, potem agregacja
EXPLAIN
SELECT SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
WHERE oi.product_id = ?;

-- 4) Zastąp NOT IN podzapytaniem półsprzężonym (lepsze plany przy NULL-ach)
-- Zamiast:
-- SELECT p.id FROM products p WHERE p.id NOT IN (SELECT oi.product_id FROM order_items oi);
-- Użyj:
SELECT p.id
FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);

Omówienie i gotowe rozwiązania

Poniżej referencyjne rozwiązania do zestawu 12 zadań. Mogą istnieć równoważne warianty (np. CTE vs. podzapytanie w FROM). Postaw na czytelność, poprawność i zgodność z PostgreSQL/MySQL 8+.

SQL
Kopiuj
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
-- (Common) Podzapytanie z wartościami zamówień
WITH order_totals AS (
  SELECT oi.order_id, SUM(oi.quantity * oi.unit_price) AS order_total
  FROM order_items oi
  GROUP BY oi.order_id
),

-- 1) Klienci z >3 zamówieniami, licznik i suma wartości
c1 AS (
  SELECT c.id, c.name, COUNT(o.id) AS orders_cnt, COALESCE(SUM(ot.order_total),0) AS revenue
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  JOIN order_totals ot ON ot.order_id = o.id
  GROUP BY c.id, c.name
  HAVING COUNT(o.id) > 3
),

-- 2) Top 5 produktów wg przychodu
c2 AS (
  SELECT p.id, p.name, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM products p
  JOIN order_items oi ON oi.product_id = p.id
  GROUP BY p.id, p.name
  ORDER BY revenue DESC
  LIMIT 5
),

-- 3) Średnia wartość zamówienia per kraj
c3 AS (
  SELECT cu.country,
         AVG(ot.order_total) AS avg_order_value
  FROM orders o
  JOIN customers cu ON cu.id = o.customer_id
  JOIN order_totals ot ON ot.order_id = o.id
  GROUP BY cu.country
),

-- 4) Zamówienia bez żadnej płatności
c4 AS (
  SELECT o.id
  FROM orders o
  LEFT JOIN payments p ON p.order_id = o.id
  WHERE p.id IS NULL
),

-- 5) Produkty nigdy nie sprzedane
c5 AS (
  SELECT p.id, p.name
  FROM products p
  WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
  )
),

-- 6) Najnowsze zamówienie per klient
c6 AS (
  SELECT o.*
  FROM orders o
  JOIN (
    SELECT customer_id, MAX(order_date) AS max_dt
    FROM orders
    GROUP BY customer_id
  ) m ON m.customer_id = o.customer_id AND m.max_dt = o.order_date
),

-- 7) Klienci z >1 kategorią zakupów
c7 AS (
  SELECT c.id, c.name,
         COUNT(DISTINCT p.category_id) AS distinct_cats
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p ON p.id = oi.product_id
  GROUP BY c.id, c.name
  HAVING COUNT(DISTINCT p.category_id) > 1
),

-- 8) Udział kategorii w przychodzie
c8 AS (
  SELECT cat.name AS category,
         SUM(oi.quantity * oi.unit_price) AS revenue_cat,
         (SELECT SUM(oi2.quantity * oi2.unit_price) FROM order_items oi2) AS revenue_all
  FROM categories cat
  JOIN products p ON p.category_id = cat.id
  JOIN order_items oi ON oi.product_id = p.id
  GROUP BY cat.name
),

-- 9) Zamówienia z >=3 pozycjami
c9 AS (
  SELECT oi.order_id
  FROM order_items oi
  GROUP BY oi.order_id
  HAVING COUNT(*) >= 3
),

-- 10) Opłacone, ale niedostarczone
c10 AS (
  SELECT DISTINCT o.id
  FROM orders o
  JOIN payments p ON p.order_id = o.id AND p.status = 'paid'
  LEFT JOIN shipments s ON s.order_id = o.id
  WHERE s.delivered_at IS NULL
),

-- 11) Rozbieżność suma pozycji vs płatności
c11 AS (
  SELECT o.id,
         ot.order_total,
         COALESCE(SUM(p.amount),0) AS paid_total,
         ABS(ot.order_total - COALESCE(SUM(p.amount),0)) AS diff
  FROM orders o
  JOIN order_totals ot ON ot.order_id = o.id
  LEFT JOIN payments p ON p.order_id = o.id AND p.status = 'paid'
  GROUP BY o.id, ot.order_total
  HAVING ABS(ot.order_total - COALESCE(SUM(p.amount),0)) > 0.01
),

-- 12) Per kraj: klient z największą wartością pojedynczego zamówienia
c12_prep AS (
  SELECT cu.country, cu.id AS customer_id, cu.name AS customer_name,
         o.id AS order_id, ot.order_total
  FROM orders o
  JOIN customers cu ON cu.id = o.customer_id
  JOIN order_totals ot ON ot.order_id = o.id
),
c12 AS (
  SELECT t.*
  FROM c12_prep t
  JOIN (
    SELECT country, MAX(order_total) AS mx FROM c12_prep GROUP BY country
  ) m ON m.country = t.country AND m.mx = t.order_total
)

-- Wyniki (wybierz to, co potrzebujesz):
-- SELECT * FROM c1;            -- Zadanie 1
-- SELECT * FROM c2;            -- Zadanie 2
-- SELECT * FROM c3;            -- Zadanie 3
-- SELECT * FROM c4;            -- Zadanie 4
-- SELECT * FROM c5;            -- Zadanie 5
-- SELECT * FROM c6;            -- Zadanie 6
-- SELECT * FROM c7;            -- Zadanie 7
-- SELECT category,
--        revenue_cat,
--        revenue_all,
--        CASE WHEN revenue_all = 0 THEN 0 ELSE revenue_cat / revenue_all END AS share
-- FROM c8;                     -- Zadanie 8
-- SELECT * FROM c9;            -- Zadanie 9
-- SELECT * FROM c10;           -- Zadanie 10
-- SELECT * FROM c11;           -- Zadanie 11
-- SELECT country, customer_id, customer_name, order_id, order_total
-- FROM c12;                    -- Zadanie 12
;