Прибыль с учетом возвратов по каналам продаж

SQL, по которому посчитано

WITH bounds AS (
  SELECT date_trunc('month', max(date)) AS month_start
  FROM sales
),
sales_month AS (
  SELECT
    s.channel,
    SUM(s.units) AS units,
    SUM(s.returned_units) AS returned_units,
    SUM(s.revenue) AS revenue,
    SUM(s.returned_revenue) AS returned_revenue,
    SUM(s.cost_of_goods) AS cost_of_goods,
    SUM(s.commission) AS commission,
    SUM(s.logistics) AS logistics
  FROM sales s
  CROSS JOIN bounds b
  WHERE s.date >= b.month_start
    AND s.date < b.month_start + INTERVAL '1 month'
  GROUP BY s.channel
),
ad_month AS (
  SELECT
    CASE a.source
      WHEN 'Продвижение WB' THEN 'Wildberries'
      WHEN 'Продвижение Ozon' THEN 'Ozon'
      WHEN 'Яндекс Директ' THEN 'Сайт'
      WHEN 'VK Реклама' THEN 'Сайт'
    END AS channel,
    SUM(a.cost) AS ad_cost
  FROM ads a
  CROSS JOIN bounds b
  WHERE a.date >= b.month_start
    AND a.date < b.month_start + INTERVAL '1 month'
  GROUP BY 1
),
calc AS (
  SELECT
    sm.channel,
    sm.revenue - sm.returned_revenue AS net_revenue,
    CASE
      WHEN sm.units = 0 THEN 0
      ELSE LEAST(1.0, COALESCE(sm.returned_units, 0)::DOUBLE / sm.units)
    END AS return_share,
    sm.cost_of_goods,
    sm.commission,
    sm.logistics,
    COALESCE(am.ad_cost, 0) AS ad_cost
  FROM sales_month sm
  LEFT JOIN ad_month am USING (channel)
)
SELECT
  channel,
  ROUND(net_revenue, 2) AS net_revenue,
  ROUND(
    net_revenue
    - cost_of_goods * (1 - return_share)
    - commission * (1 - return_share)
    - logistics * (1 - return_share)
    - ad_cost,
    2
  ) AS profit
FROM calc
ORDER BY profit DESC