Доля комиссии в выручке маркетплейсов по месяцам
SQL, по которому посчитано
WITH monthly AS (
SELECT
date_trunc('month', date) AS month,
channel,
sum(revenue) AS revenue,
sum(commission) AS commission
FROM sales
WHERE channel IN ('Wildberries', 'Ozon', 'Яндекс Маркет')
AND date >= (SELECT date_trunc('month', max(date)) FROM sales) - INTERVAL 11 MONTH
GROUP BY 1, 2
)
SELECT
strftime(month, '%Y-%m') AS month,
round(max(CASE WHEN channel = 'Wildberries' THEN commission END) / nullif(max(CASE WHEN channel = 'Wildberries' THEN revenue END), 0), 4) AS wb_share,
round(max(CASE WHEN channel = 'Ozon' THEN commission END) / nullif(max(CASE WHEN channel = 'Ozon' THEN revenue END), 0), 4) AS ozon_share,
round(max(CASE WHEN channel = 'Яндекс Маркет' THEN commission END) / nullif(max(CASE WHEN channel = 'Яндекс Маркет' THEN revenue END), 0), 4) AS yam_share
FROM monthly
GROUP BY month
ORDER BY month