SQL и Анализ данных on Telegram: “🧠 SQL-задача для опытных разработчиков В базе е…”

🧠 SQL-задача для опытных разработчиков

В базе есть таблица заказов:


orders (
  order_id INT,
  customer_id INT,
  order_date DATE,
  total_amount DECIMAL
)

Задача:
Найти всех клиентов, у которых:

- В любые 3 подряд месяца был хотя бы 1 заказ в каждом месяце,

- и суммарная сумма заказов за эти 3 месяца превысила 10,000.

Дополнительно: вывести customer_id, эти 3 месяца (в формате YYYY-MM), общее число заказов за эти месяцы и сумму.

💡 Разбор подхода:
📆 Сначала агрегируем заказы по customer_id и order_month (например, 2024-06), посчитав кол-во заказов и сумму.

🪜 Пронумеруем месяцы для каждого клиента с помощью DENSE_RANK() или ROW_NUMBER().

🧮 Найдём окна из 3 последовательных месяцев (разница в номере = 2), и для них посчитаем сумму.

🧹 Отфильтруем только те окна, где сумма > 10000 и есть 3 записи подряд.

🧩 Решение (PostgreSQL)


WITH monthly_orders AS (
  SELECT
    customer_id,
    TO_CHAR(order_date, 'YYYY-MM') AS order_month,
    DATE_TRUNC('month', order_date) AS month_start,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sum
  FROM orders
  GROUP BY customer_id, TO_CHAR(order_date, 'YYYY-MM'), DATE_TRUNC('month', order_date)
),
numbered_months AS (
  SELECT
    customer_id,
    order_month,
    month_start,
    order_count,
    total_sum,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY month_start) AS rn
  FROM monthly_orders
),
three_month_windows AS (
  SELECT
    m1.customer_id,
    m1.order_month AS month1,
    m2.order_month AS month2,
    m3.order_month AS month3,
    (m1.total_sum + m2.total_sum + m3.total_sum) AS total_window_sum,
    (m1.order_count + m2.order_count + m3.order_count) AS total_orders
  FROM numbered_months m1
  JOIN numbered_months m2 ON m1.customer_id = m2.customer_id AND m2.rn = m1.rn + 1
  JOIN numbered_months m3 ON m1.customer_id = m3.customer_id AND m3.rn = m1.rn + 2
  WHERE
    m3.month_start - m1.month_start <= INTERVAL '62 days'  -- ~2 месяца
)
SELECT
  customer_id,
  month1,
  month2,
  month3,
  total_orders,
  total_window_sum
FROM three_month_windows
WHERE total_window_sum > 10000;