Mağaza sahibi analitikə dörd sual verir: «Orta hesabla bir sifariş neçəyə başa gəlir? Müştərilər geri qayıdırmı? Onları hansı mərhələdə itiririk? Gəlirin çoxunu hansı məhsullar gətirir?». Bu sualların cavabı artıq bazadadır — sadəcə onu düzgün sorğu ilə çıxarmaq lazımdır. Bu dərsdə kursda öyrəndiyin hər şeyi — JOIN, GROUP BY, CTE, CASE, pəncərə funksiyalarını — real analitik tapşırıqlarda birləşdirəcəyik.
Əsas metrik: orta çek
Burada
- AOV
- orta çek (average order value), manat
- R
- dövr ərzində gəlir: Σ miqdar · qiymət, manat
- Nₒ
- həmin dövrdə sifarişlərin sayı
SELECT COUNT(*) AS orders,
ROUND(SUM(o.quantity * p.price), 2) AS revenue,
ROUND(SUM(o.quantity * p.price) / COUNT(*), 2) AS aov,
COUNT(DISTINCT o.customer_id) AS buyers
FROM orders AS o
JOIN products AS p ON p.id = o.product_id;Gözlənilən nəticə
orders | revenue | aov | buyers 12 | 3942.06 | 328.5 | 7
Davamını oxumaq üçün pulsuz qeydiyyatdan keç
Dərsin qalan bölmələri, test və qeydlər Educora hesabı ilə açılır. Qeydiyyat bir dəqiqə çəkir; irəliləyişin bütün cihazlarında saxlanılır.
Kohortlar və geri qayıtma
Kohort — eyni dövrdə eyni hadisəni yaşamış istifadəçilər qrupudur, məsələn, ilk sifarişini 2025-ci ilin fevralında verənlər. Bütün müştəriləri birlikdə yox, kohortlar üzrə izləmək imkan verir ki, yeni müştərilərin davranışı köhnələrinkindən ayrılsın. Ən vacib göstərici geri qayıtma (retention) — kohortun sonrakı dövrlərdə yenidən aktiv olan payıdır.
Burada
- r
- kohortun geri qayıtma faizi
- C₀
- kohortun ölçüsü — ilk dövrdə gələn müştərilər
- A
- sonrakı dövrlərdə yenidən sifariş verən müştərilər
WITH firsts AS (
SELECT customer_id, SUBSTR(MIN(order_date), 1, 7) AS cohort
FROM orders
GROUP BY customer_id
),
flags AS (
SELECT f.customer_id, f.cohort,
EXISTS (SELECT 1 FROM orders AS o
WHERE o.customer_id = f.customer_id
AND SUBSTR(o.order_date, 1, 7) > f.cohort) AS returned
FROM firsts AS f
)
SELECT cohort,
COUNT(*) AS customers,
SUM(returned) AS returned,
ROUND(100.0 * AVG(returned), 1) AS retention_pct
FROM flags
GROUP BY cohort
ORDER BY cohort;Gözlənilən nəticə
cohort | customers | returned | retention_pct 2025-01 | 1 | 1 | 100 2025-02 | 2 | 2 | 100 2025-03 | 2 | 0 | 0 2025-04 | 1 | 0 | 0 2025-06 | 1 | 0 | 0
EXISTS SQLite-da 1 və ya 0 qaytarır, ona görə SUM qayıdanları sayır, AVG isə onların payını verir.Nümunə 1: kohort cədvəlini şərh etmək
Məsələ
Yuxarıdakı nəticəyə əsasən ümumi geri qayıtma faizini hesabla. «Mart kohortu pisdir, yanvar və fevral isə əladır» nəticəsi nə dərəcədə əsaslıdır?
Həllini göstərHəllini gizlət
Həll
2) r = 3 ÷ 7 · 100% ≈ 42,9%.
3) Kohortlarda 1–2 nəfər var: bir müştəri faizi 0-dan 100-ə qədər dəyişir, statistik nəticə çıxarmaq olmaz.
4) Sonrakı kohortların geri qayıtmaq üçün vaxtı az olub (iyun kohortunu yalnız iyul izləyir) — bu, «senzurlanmış» məlumatdır. Düzgün müqayisə üçün hər kohortda eyni pəncərəyə (məsələn, ilk sifarişdən sonrakı 60 gün) baxmaq lazımdır.
Huni (funnel) və konversiya
Huni müştərinin keçdiyi ardıcıl mərhələləri göstərir; hər mərhələdə insanların bir hissəsi «düşür». Məşq bazasında hadisə jurnalı olmadığı üçün sadiqlik hunisi qururuq: qeydiyyat → ən azı 2 ədəd mal → ən azı 2 sifariş → 900 manatdan çox xərc. FIRST_VALUE başlanğıca, LAG isə əvvəlki mərhələyə nisbəti hesablayır.
Burada
- cᵢ
- i-ci mərhələyə qədər konversiya
- nᵢ
- i-ci mərhələyə çatan müştərilər
- n₁
- hunidəki ilk mərhələ
Addım konversiyası isə nᵢ ÷ nᵢ₋₁ · 100%-dir; o, «ən çox harada itiririk» sualına cavab verir.
WITH per_customer AS (
SELECT c.id,
COUNT(o.id) AS orders,
COALESCE(SUM(o.quantity), 0) AS items,
COALESCE(SUM(o.quantity * p.price), 0) AS spent
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
LEFT JOIN products AS p ON p.id = o.product_id
GROUP BY c.id
),
funnel AS (
SELECT 1 AS step, 'registered' AS stage, COUNT(*) AS customers FROM per_customer
UNION ALL SELECT 2, '2+ items', COUNT(*) FROM per_customer WHERE items >= 2
UNION ALL SELECT 3, '2+ orders', COUNT(*) FROM per_customer WHERE orders >= 2
UNION ALL SELECT 4, 'spent > 900', COUNT(*) FROM per_customer WHERE spent > 900
)
SELECT step, stage, customers,
ROUND(100.0 * customers / FIRST_VALUE(customers) OVER (ORDER BY step), 1) AS pct_of_start,
ROUND(100.0 * customers / LAG(customers) OVER (ORDER BY step), 1) AS pct_of_prev
FROM funnel
ORDER BY step;Gözlənilən nəticə
step | stage | customers | pct_of_start | pct_of_prev 1 | registered | 7 | 100 | NULL 2 | 2+ items | 6 | 85.7 | 85.7 3 | 2+ orders | 4 | 57.1 | 66.7 4 | spent > 900 | 3 | 42.9 | 75
Median və persentillər
Orta qiymət kənar qiymətlərə həssasdır: bir 1450 manatlıq noutbuk orta qiyməti xeyli yuxarı qaldırır. Median sıralanmış siyahının ortasıdır və kənar qiymətlərdən az təsirlənir. PostgreSQL-də bunun üçün hazır percentile_cont funksiyası var, SQLite-da isə median və persentilləri pəncərə funksiyaları ilə hesablayırıq. Ona görə gəlir, qiymət və müddət kimi əyilmiş paylanmalarda hesabata medianı da əlavə et.
Burada
- k
- sıralanmış siyahıda persentilin yeri (1-dən başlayaraq)
- p
- persentil, məsələn, 90
- n
- qiymətlərin sayı
«Ən yaxın rütbə» üsulu: p-ci persentil qiymətlərin ən azı p%-ni əhatə edən ən kiçik qiymətdir.
WITH ordered AS (
SELECT score,
ROW_NUMBER() OVER (ORDER BY score) AS rn,
COUNT(*) OVER () AS n
FROM enrollments
)
SELECT AVG(score) AS median_score,
(SELECT AVG(score) FROM enrollments) AS mean_score
FROM ordered
WHERE rn IN ((n + 1) / 2, (n + 2) / 2);Gözlənilən nəticə
median_score | mean_score 86.5 | 82.8
Nümunə 2: 90-cı persentil
Məsələ
20 balın 90-cı persentilini ən yaxın rütbə üsulu ilə tap. Sıralanmış ballar: 58, 64, 68, 70, 72, 75, 77, 79, 81, 85, 88, 88, 90, 91, 92, 93, 94, 95, 97, 99.
Həllini göstərHəllini gizlət
Həll
2) 18-ci qiymət: 95.
3) Yoxlama: 95-dən kiçik və ya ona bərabər 18 qiymət var, bu isə 20-nin 90%-idir.
4) SQL-də:
rn = (9 * n + 9) / 10 = (180 + 9) / 10 = 18 (tam ədəd bölməsi). PostgreSQL-in percentile_disc(0.9) funksiyası da 95 qaytarır.-- PostgreSQL: built-in ordered-set aggregates
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY score) AS median,
percentile_disc(0.9) WITHIN GROUP (ORDER BY score) AS p90
FROM enrollments;Gözlənilən nəticə
median | p90 --------+----- 86.5 | 95 (1 row)
percentile_cont qonşu qiymətlər arasında interpolyasiya edir, percentile_disc isə mövcud qiymətlərdən birini qaytarır.Mini layihə: məhsulların ABC analizi
Pareto prinsipinə görə gəlirin böyük hissəsini adətən məhsulların kiçik hissəsi gətirir. ABC analizi bunu ölçür: məhsulları gəlirə görə azalan sırada düzürük, artan payı hesablayırıq və sinif veririk: əvvəlki məhsulların payı 80%-ə çatmayıbsa — A, 95%-ə çatmayıbsa — B, qalanları — C. Addımlar: 1) CTE-də hər məhsulun gəliri; 2) pəncərə ilə artan cəm və ümumi cəm; 3) CASE ilə sinif.
WITH revenue AS (
SELECT p.name, ROUND(SUM(o.quantity * p.price), 2) AS revenue
FROM orders AS o
JOIN products AS p ON p.id = o.product_id
GROUP BY p.id, p.name
),
running AS (
SELECT name, revenue,
SUM(revenue) OVER (ORDER BY revenue DESC, name
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum,
SUM(revenue) OVER () AS total
FROM revenue
)
SELECT name, revenue,
ROUND(100.0 * cum / total, 1) AS cum_pct,
CASE WHEN (cum - revenue) < 0.80 * total THEN 'A'
WHEN (cum - revenue) < 0.95 * total THEN 'B'
ELSE 'C' END AS abc_class
FROM running
ORDER BY revenue DESC, name;Gözlənilən nəticə
name | revenue | cum_pct | abc_class Smartphone | 1799.98 | 45.7 | A Laptop | 1450 | 82.4 | A Headphones | 361.5 | 91.6 | B Notebook | 96 | 94 | B Desk lamp | 69.98 | 95.8 | B Backpack | 55 | 97.2 | C Chess set | 42 | 98.3 | C Water bottle | 36 | 99.2 | C Pen set | 31.6 | 100 | C
Nəticə: 9 satılan məhsuldan ikisi (22%) gəlirin 82%-ni gətirir — klassik Pareto mənzərəsi. A sinfi üçün anbar qalığına və tədarükə xüsusi nəzarət, C sinfi üçün isə çeşidin sadələşdirilməsi və ya birgə aksiyalar düşünülə bilər. Yadda saxla: 12 sifariş əsasında bu, yalnız metodun nümayişidir; real qərar üçün aylarla toplanmış məlumat lazımdır.
Tapşırıq
Hər ölkə üçün sifarişlərin sayını (orders), gəliri (revenue, 2 onluq rəqəm) və orta çeki (aov = gəlir ÷ sifarişlər, 2 onluq rəqəm) hesabla. Nəticəni gəlirə görə azalan sırada düz.
SELECT c.country
-- orders, revenue, aov
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN products AS p ON p.id = o.product_id
GROUP BY c.country
ORDER BY c.country;Gözlənilən nəticə
country | orders | revenue | aov Azerbaijan | 7 | 2843.49 | 406.21 Türkiye | 3 | 973.59 | 324.53 United Kingdom | 1 | 69.98 | 69.98 Russia | 1 | 55 | 55
Tapşırıq
Pəncərə funksiyaları ilə məhsul qiymətlərinin medianını (median_price) tap və yanında 2 onluq rəqəmə qədər yuvarlaqlaşdırılmış orta qiyməti (avg_price) göstər. Median ortadan niyə bu qədər kiçikdir?
WITH ordered AS (
SELECT price
-- row number and total count
FROM products
)
SELECT AVG(price) AS median_price
FROM ordered
-- keep only the middle row(s)
;Gözlənilən nəticə
median_price | avg_price 48.5 | 293.56
Yadda saxla
- Metriklər düsturlardan başlayır: orta çek = gəlir ÷ sifarişlər, geri qayıtma = qayıdanlar ÷ kohort, konversiya = nᵢ ÷ n₁.
- Kohort ilk hadisənin dövrünə görə qurulur (
MIN(order_date)), geri qayıtma isəEXISTSvə ya şərti aqreqasiya ilə sayılır. - Huni
UNION ALLilə mərhələlərdən, konversiya isəFIRST_VALUEvəLAGilə hesablanır. - Median kənar qiymətlərə davamlıdır; SQLite-da
ROW_NUMBERvəCOUNT(*) OVER ()ilə, PostgreSQL-dəpercentile_contilə hesablanır. - Faizin yanında həmişə mütləq ədədi göstər və kohortları eyni müşahidə pəncərəsi ilə müqayisə et.