Выручка сайта и сумма выигранных сделок из CRM по месяцам
SQL, по которому посчитано
WITH site AS (
SELECT date_trunc('month', date)::DATE AS sale_month,
sum(revenue) AS site_revenue
FROM sales
WHERE channel = 'Сайт'
AND date > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
GROUP BY 1
),
crm AS (
SELECT date_trunc('month', date)::DATE AS sale_month,
sum(amount) AS crm_revenue
FROM deals
WHERE source = 'Сайт'
AND status = 'Выиграна'
AND date > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
GROUP BY 1
)
SELECT s.sale_month AS sale_month,
round(s.site_revenue) AS site_revenue,
round(coalesce(c.crm_revenue, 0)) AS crm_revenue,
round(s.site_revenue - coalesce(c.crm_revenue, 0)) AS diff_rub,
round((s.site_revenue - coalesce(c.crm_revenue, 0)) / s.site_revenue, 3) AS diff_share
FROM site s
LEFT JOIN crm c ON c.sale_month = s.sale_month
ORDER BY s.sale_month