Средняя наценка по категориям за последний квартал
SQL, по которому посчитано
WITH bounds AS (
SELECT
max(date) AS end_date,
date_trunc('quarter', max(date)) AS start_date
FROM sales
),
agg AS (
SELECT
s.category,
sum(s.revenue - coalesce(s.returned_revenue, 0)) AS net_revenue,
sum(
s.cost_of_goods * (
1 - least(coalesce(s.returned_units::DOUBLE / nullif(s.units, 0), 0), 1)
)
) AS net_cost
FROM sales AS s
CROSS JOIN bounds AS b
WHERE s.date >= b.start_date
AND s.date <= b.end_date
GROUP BY s.category
)
SELECT
category,
round((net_revenue - net_cost) / nullif(net_cost, 0), 4) AS markup
FROM agg
WHERE net_cost > 0
ORDER BY markup DESC