Стоимость заявки с сайта
SQL, по которому посчитано
WITH spend AS (
SELECT date_trunc('month', date) AS month, sum(cost) AS cost
FROM ads
WHERE source IN ('Яндекс Директ', 'VK Реклама')
GROUP BY month
),
leads AS (
SELECT date_trunc('month', date) AS month, count(*) AS leads
FROM deals
WHERE source = 'Сайт'
GROUP BY month
)
SELECT spend.month, round(spend.cost / leads.leads) AS cpa
FROM spend
JOIN leads USING (month)
WHERE spend.month > (SELECT max(date) FROM ads) - INTERVAL 12 MONTH
ORDER BY spend.month