Выручка от новых и повторных клиентов по месяцам
SQL, по которому посчитано
WITH first_deal AS (
SELECT customer_id, min(date) AS first_date
FROM deals
WHERE status = 'Выиграна'
GROUP BY customer_id
),
monthly AS (
SELECT date_trunc('month', d.date) AS month,
CASE WHEN d.date = f.first_date THEN 'Новые' ELSE 'Повторные' END AS client_type,
sum(d.amount) AS revenue
FROM deals AS d
JOIN first_deal AS f ON d.customer_id = f.customer_id
WHERE d.status = 'Выиграна'
AND d.date > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
GROUP BY 1, 2
)
SELECT month,
client_type,
round(revenue) AS revenue
FROM monthly
ORDER BY month, client_type