Доля выручки с рекламы по категориям за последний квартал
SQL, по которому посчитано
WITH bounds AS (
SELECT date_trunc('quarter', max(date)) AS start_date
FROM ads
),
ad_rev AS (
SELECT
category,
sum(revenue) AS ad_revenue
FROM ads
CROSS JOIN bounds
WHERE ads.date >= bounds.start_date
AND ads.category <> 'Все'
GROUP BY category
),
sales_rev AS (
SELECT
category,
sum(revenue) AS total_revenue
FROM sales
CROSS JOIN bounds
WHERE sales.date >= bounds.start_date
GROUP BY category
)
SELECT
s.category,
round(coalesce(a.ad_revenue, 0), 0) AS ad_revenue,
round(s.total_revenue, 0) AS total_revenue,
round(coalesce(a.ad_revenue, 0) / nullif(s.total_revenue, 0), 4) AS ad_share
FROM sales_rev AS s
LEFT JOIN ad_rev AS a
ON s.category = a.category
ORDER BY ad_share DESC NULLS LAST
LIMIT 30