Условие

Есть таблица transact с транзакциями клиентов с 2021 года:

id_client — id клиента;
tran_time — время транзакции;
id_tran — id транзакции;
sum_tran — сумма транзакции.

Напишите SQL:

Траты каждого клиента за последние 30 дней от текущего числа.
Для топ-100 наиболее платежеспособных клиентов за всю историю — траты помесячно.

Решение

Подход

Q1 — простая агрегация с фильтром по дате.

Q2 — сначала найти топ-100 (через ORDER BY ... LIMIT 100 или ранжирование), затем сджойнить с месячной агрегацией.

Реализация

-- Q1: траты за последние 30 дней
SELECT
    id_client,
    SUM(sum_tran) AS spent_30d,
    COUNT(*)      AS tx_30d
FROM transact
WHERE tran_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY id_client;

-- Q2: топ-100 клиентов за всю историю и их траты помесячно
WITH top100 AS (
    SELECT id_client
    FROM transact
    GROUP BY id_client
    ORDER BY SUM(sum_tran) DESC
    LIMIT 100
)
SELECT
    t.id_client,
    DATE_TRUNC('month', t.tran_time) AS month_start,
    SUM(t.sum_tran)                   AS month_spent,
    COUNT(*)                          AS month_tx
FROM transact t
JOIN top100 USING (id_client)
GROUP BY t.id_client, DATE_TRUNC('month', t.tran_time)
ORDER BY t.id_client, month_start;

Анализ результата

В Q1 «текущее число» — это сегодняшняя дата. Если нужно «30 дней от вчерашнего» — заменить CURRENT_DATE на CURRENT_DATE - 1.
В Q2 если нужны клиенты, у которых нет транзакций в каком-то месяце — придётся CROSS JOIN с сеткой месяцев и LEFT JOIN. Иначе «дыры» в истории не показаны.
Топ-100 определяется по сумме за всю историю — у новых клиентов с маленькой активностью шанс попасть нулевой; это и есть «платежеспособные».

Расширенная Q2 (с дырами)

WITH top100 AS (
    SELECT id_client
    FROM transact
    GROUP BY id_client
    ORDER BY SUM(sum_tran) DESC
    LIMIT 100
),
months AS (
    SELECT generate_series(
        DATE_TRUNC('month', (SELECT MIN(tran_time) FROM transact)),
        DATE_TRUNC('month', CURRENT_DATE),
        INTERVAL '1 month'
    ) AS month_start
),
grid AS (
    SELECT t.id_client, m.month_start
    FROM top100 t
    CROSS JOIN months m
)
SELECT
    g.id_client,
    g.month_start,
    COALESCE(SUM(t.sum_tran), 0) AS month_spent,
    COUNT(t.id_tran)             AS month_tx
FROM grid g
LEFT JOIN transact t
       ON t.id_client = g.id_client
      AND DATE_TRUNC('month', t.tran_time) = g.month_start
GROUP BY g.id_client, g.month_start
ORDER BY g.id_client, g.month_start;

Подводные камни

«Платежеспособный». Это сумма? Среднее? Частота? В задаче — сумма за всю историю, но в реальности product manager имеет ввиду что-то иное; уточняйте.
DATE_TRUNC диалекты. В MySQL — DATE_FORMAT(tran_time, '%Y-%m-01'), в Snowflake — DATE_TRUNC('MONTH', tran_time), в ClickHouse — toStartOfMonth. Проверяйте под свой движок.
Тай-брейки в TOP-100. Если у 102-го и 100-го клиентов одинаковая сумма — нестабильно. Добавляйте ORDER BY SUM(sum_tran) DESC, id_client.
Возвраты. Если sum_tran бывает отрицательной (refund) — «топ-100 платежеспособных» по сумме может ошибаться. Используйте SUM(GREATEST(sum_tran, 0)) или фильтр.
Часовой пояс. CURRENT_DATE берётся в TZ сессии; на проде надо явно at time zone 'UTC'.
Производительность. Без индекса по (id_client, tran_time) — full scan. На больших таблицах партиционируем по месяцу.

Эталонный ответ

Q1: WHERE tran_time >= CURRENT_DATE - INTERVAL '30 days' GROUP BY id_client.

Q2: CTE с топ-100 (ORDER BY SUM DESC LIMIT 100) → джойн → DATE_TRUNC('month', ...) → SUM(sum_tran). Для полноты помесячного отчёта (включая месяцы без покупок) — CROSS JOIN сетки месяцев + LEFT JOIN.

МТС: траты клиентов за 30 дней и помесячные траты топ-100 платежеспособных