Доля выручки по группам покупателей
SQL, по которому посчитано
WITH base AS (
SELECT
customer_id,
sum(amount) AS revenue
FROM deals
WHERE status = 'Выиграна'
AND "date" >= (SELECT date_trunc('month', max("date")) FROM deals) - INTERVAL 12 MONTH
AND "date" <= (SELECT max("date") FROM deals)
GROUP BY customer_id
),
ranked AS (
SELECT
customer_id,
revenue,
row_number() OVER (ORDER BY revenue DESC) AS rn,
count(*) OVER () AS customers_total,
sum(revenue) OVER () AS revenue_total
FROM base
WHERE revenue > 0
),
buckets AS (
SELECT
CAST(ceil(rn * 10.0 / customers_total) AS INTEGER) AS decile,
count(*) AS customers,
sum(revenue) AS revenue,
sum(revenue) / max(revenue_total) AS revenue_share
FROM ranked
GROUP BY decile
)
SELECT
decile,
customers,
round(revenue, 0) AS revenue,
round(revenue_share, 4) AS revenue_share,
round(sum(revenue_share) OVER (ORDER BY decile), 4) AS cumulative_share
FROM buckets
ORDER BY decile