/
spirzen
/
it-code-examples
Обзор
Документация
Войти
/
spirzen
/
it-code-examples
Код
Запросы
0
Задачи
Вики
Пакеты
0
Релизы
0
Аналитика
Безопасность
master
examples/sql/sql-7-002/part-02.sql
29 строк
785 B
Spirzen
Code migration pack
09 июн 2026, 01:41
09 июн 2026, 01:41
cebc284
Код
Авторство
О чём код?
WITH product_revenue AS ( SELECT p.name, SUM(oi.quantity * oi.unit_price) AS revenue FROM shop_data.order_items oi JOIN shop_data.products p ON p.product_id = oi.product_id GROUP BY p.name ), ranked AS ( SELECT name, revenue, SUM(revenue) OVER () AS total, SUM(revenue) OVER (ORDER BY revenue DESC) AS cumulative, ROUND( 100.0 * SUM(revenue) OVER (ORDER BY revenue DESC) / SUM(revenue) OVER (), 1 ) AS cumulative_pct FROM product_revenue ) SELECT name, revenue, cumulative_pct, CASE WHEN cumulative_pct <= 80 THEN 'A' WHEN cumulative_pct <= 95 THEN 'B' ELSE 'C' END AS abc_class FROM ranked ORDER BY revenue DESC;