Skip to content
Educora
EN
AzərbaycancaEnglishРусскийTürkçe
My learningEducora StudioSettings
University30 min22 / 22

Data analysis with SQL

Calculate business metrics with SQL: average order value, cohorts and retention, funnel conversion, medians and percentiles, and finally an ABC analysis project on the practice data.

Check yourself
In this lesson you will learn
  • Turn the formulas for average order value, retention and conversion into SQL queries
  • Build a cohort table and a funnel, and interpret the results with their statistical limits in mind
  • Calculate the median, percentiles and an ABC analysis with window functions

A shop owner asks an analyst four questions: “How much does an average order cost? Do customers come back? At which stage do we lose them? Which products bring in most of the revenue?” The answers are already in the database — they just need to be extracted with the right query. In this lesson we combine everything from the course — JOIN, GROUP BY, CTEs, CASE, window functions — in real analytical tasks.

A key metric: average order value

AOV = R ÷ Nₒ
where:
  • AOVaverage order value, manat
  • Rrevenue for the period: Σ quantity · price, manat
  • Nₒthe number of orders in that period
SQL
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;
▸ Expected output
orders | revenue | aov | buyers
12 | 3942.06 | 328.5 | 7

Cohorts and retention

A cohort is a group of users who experienced the same event in the same period — for example, those who placed their first order in February 2025. Tracking customers by cohort rather than all together separates the behaviour of new customers from that of older ones. The most important indicator is retention — the share of a cohort that is active again in later periods.

r = A ÷ C₀ · 100%
where:
  • rthe cohort's retention rate
  • C₀the cohort size — customers who arrived in the first period
  • Acustomers who ordered again in a later period
SQL
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;
▸ Expected output
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
In SQLite EXISTS returns 1 or 0, so SUM counts the returning customers and AVG gives their share.
Example 1: interpreting the cohort table

Using the result above, calculate the overall retention rate. How well founded is the conclusion “the March cohort is bad, while January and February are excellent”?

Show solution
1) C₀ = 1 + 2 + 2 + 1 + 1 = 7 customers, A = 1 + 2 = 3.
2) r = 3 ÷ 7 · 100% ≈ 42.9%.
3) The cohorts contain 1–2 people: a single customer moves the percentage from 0 to 100, so no statistical conclusion is possible.
4) Later cohorts have had less time to return (the June cohort is followed only by July) — this is “censored” data. A fair comparison looks at the same window for every cohort (for example, 60 days after the first order).

Funnels and conversion

A funnel shows the consecutive stages a customer goes through; at every stage some people “drop out”. Our practice database has no event log, so we build a loyalty funnel: signed up → at least 2 items → at least 2 orders → spent more than 900 manat. FIRST_VALUE computes the ratio to the start, and LAG the ratio to the previous stage.

cᵢ = nᵢ ÷ n₁ · 100%
where:
  • cᵢconversion up to stage i
  • nᵢcustomers who reached stage i
  • n₁the first stage of the funnel

Step conversion is nᵢ ÷ nᵢ₋₁ · 100%; it answers the question “where do we lose the most?”.

SQL
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;
▸ Expected output
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
The biggest loss is between stages 2 and 3: encouraging a repeat order (a discount coupon, a reminder) may be the most useful step.

Medians and percentiles

The mean is sensitive to outliers: a single 1450-manat laptop pulls the average price far up. The median is the middle of a sorted list and is barely affected by outliers. PostgreSQL has a ready-made percentile_cont function for this, while in SQLite we calculate the median and percentiles with window functions. So for skewed distributions such as revenue, prices and durations, add the median to your report as well.

k = ⌈ p ÷ 100 · n ⌉
where:
  • kthe position of the percentile in the sorted list (starting from 1)
  • pthe percentile, e.g. 90
  • nthe number of values

The nearest-rank method: the p-th percentile is the smallest value that covers at least p% of the values.

SQL
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);
▸ Expected output
median_score | mean_score
86.5 | 82.8
The mean (82.8) is below the median (86.5): a few low scores (58, 64, 68) pull the mean down.
Example 2: the 90th percentile

Find the 90th percentile of the 20 scores with the nearest-rank method. Sorted scores: 58, 64, 68, 70, 72, 75, 77, 79, 81, 85, 88, 88, 90, 91, 92, 93, 94, 95, 97, 99.

Show solution
1) k = ⌈90 ÷ 100 · 20⌉ = ⌈18⌉ = 18.
2) The 18th value: 95.
3) Check: 18 values are less than or equal to 95, which is 90% of 20.
4) In SQL: rn = (9 * n + 9) / 10 = (180 + 9) / 10 = 18 (integer division). PostgreSQL's percentile_disc(0.9) also returns 95.
SQL
-- 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;
Expected output
 median | p90
--------+-----
   86.5 |  95
(1 row)
PostgreSQL, not runnable. percentile_cont interpolates between neighbouring values, while percentile_disc returns one of the existing values.

Mini project: ABC analysis of products

According to the Pareto principle, a small share of products usually brings a large share of revenue. ABC analysis measures this: we sort products by revenue in descending order, compute the cumulative share and assign a class: A if the products before it have not reached 80% yet, B if they have not reached 95%, and C for the rest. Steps: 1) revenue per product in a CTE; 2) a running total and the grand total with windows; 3) the class with CASE.

SQL
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;
▸ Expected output
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

The result: two of the 9 products sold (22%) bring 82% of the revenue — a classic Pareto picture. Class A deserves special control of stock and supply, while for class C you might simplify the range or run bundle promotions. Remember: with 12 orders this only demonstrates the method; a real decision needs data collected over months.

Exercise

For each country calculate the number of orders (orders), the revenue (revenue, 2 decimal places) and the average order value (aov = revenue ÷ orders, 2 decimal places). Sort by revenue descending.

Exercise · SQL
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;
▸ Expected output
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
Exercise

Use window functions to find the median of the product prices (median_price) and show the mean price rounded to 2 decimal places (avg_price) next to it. Why is the median so much smaller than the mean?

Exercise · SQL
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)
;
▸ Expected output
median_price | avg_price
48.5 | 293.56

Key points

  • Metrics start from formulas: AOV = revenue ÷ orders, retention = returning ÷ cohort, conversion = nᵢ ÷ n₁.
  • A cohort is built from the period of the first event (MIN(order_date)), and returning customers are counted with EXISTS or conditional aggregation.
  • A funnel is built from stages with UNION ALL, and conversion is computed with FIRST_VALUE and LAG.
  • The median is robust to outliers; it is computed with ROW_NUMBER and COUNT(*) OVER () in SQLite and with percentile_cont in PostgreSQL.
  • Always show the absolute count next to a percentage and compare cohorts over the same observation window.

Check yourself

10 questions. Every correct answer earns XP.

1 / 10
Revenue for the period is 3942.06 manat and there are 12 orders. What is the average order value?