Клиенты, вернувшиеся после проигранной сделки, по месяцам
SQL, по которому посчитано
WITH bounds AS (
SELECT max(date) AS max_date FROM deals
),
period AS (
SELECT max_date, max_date - INTERVAL 12 MONTH AS start_date FROM bounds
),
first_loss AS (
SELECT customer_id, min(date) AS loss_date
FROM deals
WHERE status = 'Проиграна'
GROUP BY customer_id
),
returns AS (
SELECT d.customer_id, d.date
FROM deals d
JOIN first_loss f ON f.customer_id = d.customer_id
JOIN period p ON TRUE
WHERE d.status = 'Выиграна'
AND d.date > f.loss_date
AND d.date >= p.start_date
AND d.date <= p.max_date
)
SELECT date_trunc('month', date)::DATE AS month,
count(DISTINCT customer_id) AS returned_customers
FROM returns
GROUP BY 1
ORDER BY 1