Средняя сумма второй покупки по источникам
SQL, по которому посчитано
WITH won_deals AS (
SELECT
customer_id,
deal_id,
date,
source,
amount,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY date, deal_id
) AS purchase_number
FROM deals
WHERE status = 'Выиграна'
),
second_purchases AS (
SELECT
source,
amount
FROM won_deals
WHERE purchase_number = 2
AND date >= (
SELECT date_trunc('month', max(date)) - INTERVAL '11 months'
FROM deals
)
)
SELECT
source,
round(avg(amount), 2) AS avg_second_amount
FROM second_purchases
GROUP BY source
ORDER BY avg_second_amount DESC