Revenue Total Within Each Status — Window Functions in SQL

The problem

Brightlane's revenue report shows each order's amount alongside the total revenue for the order's status group.

Write a query to return the ID, status, and amount of every order, plus the total revenue across every order in that status on each row.

Assumptions:

The orders table has one row per order with an id, a status, and a total_amount.
A status's total revenue is the combined total_amount across every order in that status. The same value should appear on every row that shares a status.

Output:

One row per order, with columns id, status, total_amount, and status_total.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  id,
  status,
  total_amount,
  SUM(total_amount) OVER (
    PARTITION BY
      status
  ) AS status_total
FROM
  orders

The shape

SUM(total_amount) OVER (PARTITION BY status) computes a separate revenue total for each status group and writes that group's total onto every order in the group. Pending orders see the pending total; shipped orders see the shipped total. Every individual order stays in the output.

Clause by clause

SELECT id, status, total_amount returns each order's identifier, status, and individual amount, one row per order.
The window column is:

SUM(total_amount) OVER (PARTITION BY status) AS status_total

PARTITION BY status splits the row set into one group per distinct status value. SUM(total_amount) runs inside each group independently, so the value attached to a given order is the combined total_amount across every order that shares its status. All shipped orders see the same status_total; pending, delivered, and cancelled orders each see their own.

FROM orders reads every order. The revenue report compares each order to its status group's total, so every row stays in.

Why this and not `GROUP BY status`

GROUP BY status would return one row per status value with the group total attached, and the individual orders would be gone. The report needs each order and its group's total on the same row. The window form computes the same totals as the GROUP BY version, but PARTITION BY keeps every input row in the output, with the per-group aggregate replicated across rows that share a status.

You practiced partitioning a SUM window by a categorical column — every row sees its own partition's total alongside its individual value, no row collapsing.

Return the ID, status, and amount of every order, plus the total revenue across every order in that status on each row

The shape

Clause by clause

Why this and not `GROUP BY status`

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

The shape

Clause by clause

Why this and not GROUP BY status

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

Why this and not `GROUP BY status`