Активные клиенты по месяцам, вымышленный мебельный магазин
SQL, по которому посчитано
WITH bounds AS (
SELECT
date_trunc('month', max(date))::DATE AS last_month,
max(date) AS last_date
FROM deals
),
monthly AS (
SELECT
date_trunc('month', d.date)::DATE AS month,
COUNT(DISTINCT d.customer_id) AS active_customers
FROM deals d
CROSS JOIN bounds b
WHERE d.status = 'Выиграна'
AND d.date >= b.last_month - INTERVAL '11 months'
AND d.date <= b.last_date
GROUP BY 1
)
SELECT
month,
active_customers
FROM monthly
ORDER BY month