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