Skip to content
Quantoros

Return productname, revenue (quantity price summed), and cumulative percent of total revenue when products are ranked…

SQLLevel: IntermediateWindow functions

Return product_name, revenue (quantity * price summed), and cumulative percent of total revenue when products are ranked by revenue descending, rounded to 1 decimal, highest first. Break revenue ties by product name.

Table definitions (from the source exercise):

CREATE TABLE products(product_id INTEGER PRIMARY KEY, product_name TEXT, category TEXT, price REAL);
CREATE TABLE order_items(item_id INTEGER PRIMARY KEY, order_id INTEGER, product_id INTEGER, quantity INTEGER);
Hints
  • Define one output row and the source-table keys.
  • Check duplicates, NULLs and the order of filtering and aggregation.

Answer

Approach: Per-product CTE, then running SUM OVER (ORDER BY revenue DESC) divided by SUM OVER ().

WITH pr AS (
  SELECT p.product_name, SUM(oi.quantity * p.price) AS revenue
  FROM order_items oi
  JOIN products p ON p.product_id = oi.product_id
  GROUP BY p.product_id, p.product_name
)
SELECT product_name, revenue,
       ROUND(100.0 * SUM(revenue) OVER (ORDER BY revenue DESC, product_name)
             / SUM(revenue) OVER (), 1) AS cum_pct
FROM pr
ORDER BY revenue DESC, product_name;

The source uses SQLite with a MySQL compatibility layer. Date and string functions are dialect dependent; the query is preserved as published.

Source and licence: SQL Practice Lab — Ebrahim Sharifi · MIT

Related questions