Средняя и медианная сумма первой покупки по источникам, руб.
SQL, по которому посчитано
WITH won AS (
SELECT
customer_id,
date,
source,
amount,
row_number() OVER (PARTITION BY customer_id ORDER BY date, deal_id) AS rn
FROM deals
WHERE status = 'Выиграна'
),
first_deal AS (
SELECT customer_id, date, source, amount
FROM won
WHERE rn = 1
)
SELECT
source,
count(*) AS customers,
round(avg(amount), 0) AS avg_first_amount,
round(median(amount), 0) AS median_first_amount
FROM first_deal
WHERE date >= (SELECT max(date) - INTERVAL 12 MONTH FROM deals)
GROUP BY source
ORDER BY avg_first_amount DESC