Сумма проигранных сделок по менеджерам за 12 месяцев
SQL, по которому посчитано
WITH bounds AS (
SELECT max(date) AS max_date,
max(date) - INTERVAL '12 months' AS min_date
FROM deals
),
lost AS (
SELECT d.manager,
sum(d.amount) AS lost_amount,
count(*) AS lost_deals
FROM deals d, bounds b
WHERE d.date >= b.min_date
AND d.date <= b.max_date
AND d.status = 'Проиграна'
GROUP BY d.manager
),
total AS (
SELECT sum(lost_amount) AS total_amount
FROM lost
)
SELECT l.manager,
round(l.lost_amount) AS lost_amount,
l.lost_deals,
round(l.lost_amount / nullif(t.total_amount, 0), 4) AS lost_share
FROM lost l, total t
ORDER BY lost_amount DESC
LIMIT 30