Прибыль по категориям за последний квартал
SQL, по которому посчитано
WITH bounds AS (
SELECT
date_trunc('month', max(date)) - INTERVAL '2 months' AS start_date,
date_trunc('month', max(date)) + INTERVAL '1 month' AS end_date
FROM sales
),
sales_agg AS (
SELECT
category,
SUM(revenue - returned_revenue) AS net_revenue,
SUM(cost_of_goods) AS cogs,
SUM(commission) AS commission,
SUM(logistics) AS logistics
FROM sales, bounds
WHERE date >= bounds.start_date AND date < bounds.end_date
GROUP BY category
),
ads_total AS (
SELECT SUM(cost) AS ad_cost
FROM ads, bounds
WHERE date >= bounds.start_date AND date < bounds.end_date
),
total_revenue AS (
SELECT SUM(net_revenue) AS net_revenue
FROM sales_agg
),
profit_calc AS (
SELECT
s.category,
s.net_revenue,
s.net_revenue - s.cogs - s.commission - s.logistics
- (a.ad_cost * s.net_revenue / NULLIF(t.net_revenue, 0)) AS profit
FROM sales_agg s
CROSS JOIN ads_total a
CROSS JOIN total_revenue t
)
SELECT
category,
round(net_revenue, 0) AS net_revenue,
round(profit, 0) AS profit,
round(profit / NULLIF(SUM(profit) OVER (), 0), 4) AS profit_share
FROM profit_calc
ORDER BY profit DESC
LIMIT 30