Клиенты по источникам за 12 месяцев
SQL, по которому посчитано
WITH won AS (
SELECT
customer_id,
source,
date,
row_number() OVER (PARTITION BY customer_id ORDER BY date, deal_id) AS rn
FROM deals
WHERE status = 'Выиграна'
),
first_won AS (
SELECT customer_id, source, date
FROM won
WHERE rn = 1
)
SELECT
source,
count(*)::INTEGER AS clients
FROM first_won
WHERE date >= (
SELECT CAST(date_trunc('month', max(date)) - INTERVAL '11 months' AS DATE)
FROM deals
)
GROUP BY source
ORDER BY clients DESC