Доля проигранных сделок по менеджерам за последний квартал
SQL, по которому посчитано
WITH bounds AS (
SELECT date_trunc('month', max(date)) AS month_start
FROM deals
),
closed AS (
SELECT d.manager, d.status, d.amount
FROM deals d
CROSS JOIN bounds b
WHERE d.date >= b.month_start - INTERVAL 2 MONTH
AND d.date < b.month_start + INTERVAL 1 MONTH
AND d.status IN ('Выиграна', 'Проиграна')
)
SELECT
manager,
COUNT(*) AS closed_deals,
COUNT(*) FILTER (WHERE status = 'Проиграна') AS lost_deals,
ROUND(COUNT(*) FILTER (WHERE status = 'Проиграна')::DOUBLE / NULLIF(COUNT(*), 0), 4) AS lost_share,
ROUND(SUM(amount) FILTER (WHERE status = 'Проиграна'), 2) AS lost_amount
FROM closed
GROUP BY manager
ORDER BY lost_share DESC
LIMIT 30