Анализ данных с помощью 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;

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;

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;

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);

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;

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;

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)
;