Доля выигранных сделок по интервалам суммы
SQL, по которому посчитано
WITH bounds AS (
SELECT date_trunc('month', max(date)) AS last_month
FROM deals
),
deals_year AS (
SELECT
CASE
WHEN amount < 50000 THEN 'до 50 тыс'
WHEN amount < 100000 THEN '50-100 тыс'
WHEN amount < 200000 THEN '100-200 тыс'
WHEN amount < 400000 THEN '200-400 тыс'
ELSE 'свыше 400 тыс'
END AS amount_bucket,
CASE
WHEN amount < 50000 THEN 1
WHEN amount < 100000 THEN 2
WHEN amount < 200000 THEN 3
WHEN amount < 400000 THEN 4
ELSE 5
END AS bucket_order,
status
FROM deals, bounds
WHERE date >= bounds.last_month - INTERVAL 11 MONTH
)
SELECT
amount_bucket,
count(*) AS deals_count,
count(*) FILTER (WHERE status = 'Выиграна') AS won_count,
round(avg(CASE WHEN status = 'Выиграна' THEN 1 ELSE 0 END), 3) AS conversion
FROM deals_year
GROUP BY amount_bucket
ORDER BY min(bucket_order)