Доля выручки от новых артикулов по месяцам, вымышленный мебельный магазин
SQL, по которому посчитано
WITH first_sales AS (
SELECT
sku,
date_trunc('month', min(date)) AS first_sale_month
FROM sales
GROUP BY sku
),
sales_net AS (
SELECT
date,
sku,
revenue - coalesce(returned_revenue, 0) AS net_revenue
FROM sales
),
monthly AS (
SELECT
date_trunc('month', sn.date) AS month,
sum(sn.net_revenue) AS total_revenue,
sum(CASE
WHEN f.first_sale_month = date_trunc('month', sn.date)
THEN sn.net_revenue
ELSE 0
END) AS new_revenue
FROM sales_net sn
JOIN first_sales f ON f.sku = sn.sku
WHERE sn.date >= (
SELECT date_trunc('month', max(date)) - INTERVAL '11 months'
FROM sales
)
GROUP BY 1
)
SELECT
month,
round(new_revenue, 2) AS new_revenue,
round(total_revenue, 2) AS total_revenue,
round(new_revenue / nullif(total_revenue, 0), 4) AS share_new_revenue
FROM monthly
ORDER BY month