Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

IntermediateWindow Functions

Percentage contribution of each product to total sales

The query

SQL
SELECT
  p.name,
  SUM(oi.qty * oi.unit_price) AS product_revenue,
  SUM(SUM(oi.qty * oi.unit_price)) OVER () AS total_revenue,
  ROUND(
    SUM(oi.qty * oi.unit_price) * 100.0 /
    SUM(SUM(oi.qty * oi.unit_price)) OVER (), 2
  ) AS contribution_pct
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.name
ORDER BY contribution_pct DESC;
Tested against PostgreSQL 16

Note

“Nested aggregate + window: SUM of grouped SUMs over entire result. No subquery needed.

Tables referenced