Доля рекламной выручки в продажах по категориям за последний месяц
SQL, по которому посчитано
WITH period AS (
SELECT date_trunc('month', max(date)) AS month_start
FROM ads
),
ads_m AS (
SELECT category,
sum(cost) AS ad_cost,
sum(orders) AS ad_orders,
sum(revenue) AS ad_revenue
FROM ads
WHERE date >= (SELECT month_start FROM period)
AND date < (SELECT month_start FROM period) + INTERVAL '1 month'
AND category <> 'Все'
GROUP BY category
),
sales_m AS (
SELECT category,
sum(revenue - returned_revenue) AS sales_revenue,
sum(units - returned_units) AS sales_units
FROM sales
WHERE date >= (SELECT month_start FROM period)
AND date < (SELECT month_start FROM period) + INTERVAL '1 month'
GROUP BY category
)
SELECT coalesce(s.category, a.category) AS category,
round(coalesce(a.ad_cost, 0), 2) AS ad_cost,
coalesce(a.ad_orders, 0) AS ad_orders,
round(coalesce(a.ad_revenue, 0), 2) AS ad_revenue,
round(coalesce(s.sales_revenue, 0), 2) AS sales_revenue,
coalesce(s.sales_units, 0) AS sales_units,
round(coalesce(a.ad_revenue, 0) / nullif(s.sales_revenue, 0), 4) AS ad_revenue_share
FROM ads_m a
FULL OUTER JOIN sales_m s ON s.category = a.category
ORDER BY sales_revenue DESC NULLS LAST
LIMIT 30