Return every product that has appeared on at least one order line, with its `id`, name, the number of line items it appeared on, the total revenue it generated, and its share of total revenue across all products

The shape

Three CTEs that compute per-product revenue once, then layer a window-function denominator on top. The first names the per-line revenue, the second rolls up to per-product totals, and the third divides each product's total by SUM(revenue) OVER () — a window-function call with an empty OVER () that sums every row in the layer once and reuses that single number on every row. No second pass over the data, no scalar subquery, no second join.

Clause by clause

WITH item_details AS (
    SELECT oi.product_id, p.name AS product_name, oi.quantity * oi.unit_price AS line_revenue
    FROM order_items oi
    JOIN products p ON oi.product_id = p.id
)

The join attaches the product name to each line item and derives the row-level revenue. Both downstream layers depend on line_revenue existing, so it is named here once.

product_summary AS (
    SELECT product_id, product_name, COUNT(*) AS line_item_count, SUM(line_revenue) AS revenue
    FROM item_details
    GROUP BY product_id, product_name
)

GROUP BY product_id, product_name produces one row per product. COUNT(*) is the number of line items the product appeared on, SUM(line_revenue) is the product's total revenue. Crest Pro 14" leads with 11,994 across 6 line items.

product_shares AS (
    SELECT product_id, product_name, line_item_count, revenue,
           revenue / SUM(revenue) OVER () AS revenue_share
    FROM product_summary
)

SUM(revenue) OVER () is the window form: with an empty OVER (), the window covers every row in product_summary, so the sum is the combined revenue across every product. PostgreSQL computes that grand total once and reuses it on every row. Dividing each product's revenue by it gives the product's share as a decimal. Crest Pro 14" lands at 0.20, just over a fifth of total revenue.

• SELECT product_id, product_name, line_item_count, revenue, revenue_share FROM product_shares ORDER BY revenue DESC returns the products from biggest to smallest contributor.

Why a window-function denominator and not a scalar subquery

The same share could be written as revenue / (SELECT SUM(revenue) FROM product_summary). The result matches. The window form is the architectural choice for two reasons. It keeps the denominator inside the layer where it is consumed, which means a reader following the chain top to bottom never has to look elsewhere to see where the grand total comes from. And when the partition rule later needs to change — say, share within a category instead of share across the catalog — the change is a single PARTITION BY clause inside the existing window, not a rewrite into a correlated subquery.

The trap

The OVER () is empty on purpose. Adding a PARTITION BY product_id (or any other column with one row per product in the layer) would partition the sum down to a single row per partition, and every product's denominator would equal its own revenue. Every revenue_share would come out as 1. The empty partition is what makes the window cover every row, which is what makes the denominator the grand total.