SQLMaxx
Sign in Start free
N064-H3 Tier 5 · Expert · hard ecommerce · Brightlane

Return each order month, the revenue for that month, the cumulative revenue from the earliest month through that month, and the remaining revenue from that month through the latest month in the data

Part of Running Totals and Cumulative Metrics in SQL

The problem

Scenario: Brightlane's finance team needs both a backward-looking and forward-looking view of monthly revenue: each month should carry its own revenue plus the cumulative revenue earned to date and the revenue still to come.

Task: Write a query to return each order month, the revenue for that month, the cumulative revenue from the earliest month through that month, and the remaining revenue from that month through the latest month in the data.

Assumptions:

  • An order month is identified by its first day.
  • A month's monthly_revenue is the combined total_amount across orders placed in that month.
  • A month's cumulative_revenue is the combined monthly_revenue from the earliest month through that month inclusive.
  • A month's remaining_revenue is the combined monthly_revenue from that month through the latest month in the data inclusive.

Output:

  • One row per order month present in the data.
  • Columns in this order: order_month, monthly_revenue, cumulative_revenue, remaining_revenue.
  • Sorted by order_month ascending.
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
  DATE_TRUNC('month', ordered_at) AS order_month,
  SUM(total_amount) AS monthly_revenue,
  SUM(SUM(total_amount)) OVER (
    ORDER BY
      DATE_TRUNC('month', ordered_at) ROWS BETWEEN UNBOUNDED PRECEDING
      AND CURRENT ROW
  ) AS cumulative_revenue,
  SUM(SUM(total_amount)) OVER (
    ORDER BY
      DATE_TRUNC('month', ordered_at) ROWS BETWEEN CURRENT ROW
      AND UNBOUNDED FOLLOWING
  ) AS remaining_revenue
FROM
  orders
GROUP BY
  DATE_TRUNC('month', ordered_at)
ORDER BY
  order_month

The shape

Two windows on the same partition look in opposite directions. The cumulative window uses ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — every row from the start of the data through the current month. The remaining window uses ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING — the current month through the last month in the data. Both frames include the current row, so the two numbers double-count the current month's revenue by design, and their values overlap by exactly monthly_revenue at every row.

Clause by clause

  • SELECT DATE_TRUNC('month', ordered_at) AS order_month, SUM(total_amount) AS monthly_revenue produces one row per order month with that month's revenue.
  • SUM(SUM(total_amount)) OVER (ORDER BY DATE_TRUNC('month', ordered_at) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_revenue runs the backward-looking accumulation from the earliest month through the current month.
  • SUM(SUM(total_amount)) OVER (ORDER BY DATE_TRUNC('month', ordered_at) ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS remaining_revenue runs the forward-looking accumulation from the current month through the latest month. UNBOUNDED FOLLOWING anchors the right side of the frame at the last row in the ordered partition.
  • FROM orders GROUP BY DATE_TRUNC('month', ordered_at) aggregates the raw orders to one row per month so both windows operate on month-level rows.
  • ORDER BY order_month sorts the final output chronologically.

Why two windows and not total - cumulative + monthly

Computing remaining_revenue as total_revenue - cumulative_revenue + monthly_revenue would work numerically, but it requires materializing the total revenue across all months as a separate value and joining it back in. A second window function with CURRENT ROW AND UNBOUNDED FOLLOWING does the work in the same pass and reads as the direct symmetric of the cumulative window. The shape of the SQL matches the shape of the question: one frame looks backward, one looks forward, both anchored at the current row.

The trap

CURRENT ROW is included in both frames, so cumulative_revenue + remaining_revenue does not equal total revenue — it equals total revenue plus the current month's revenue (because the current month is counted twice, once as the end of the cumulative window and once as the start of the remaining window). This is the prompt's intended behavior: each row's remaining_revenue carries the current month's revenue forward as part of "from that month through the latest month," and each row's cumulative_revenue includes the current month as part of "through that month inclusive." Switching either frame to 1 PRECEDING or 1 FOLLOWING would remove the overlap but would also change what the column means. The double-count is structural, not a mistake.

You practiced pairing a backward-looking window (unbounded preceding) with a forward-looking window (unbounded following) on the same metric, so each row carries both the earned-to-date and remaining totals inline.

How you actually get good at SQL

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

That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.

A stack of SQL practice problem cards, the top card showing an employees table.
615 problems · 66 concepts

Real problems. Not toy examples.

615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.

A retro computer showing a SQL query marked correct with a green checkmark.
Instant AI feedback

Write a query. Know if it's right in one second.

No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.

A circular mastery progress dial filling from blue to green, the SQLMaxx diamond at its center.
Mastery tracking

Stop guessing whether you actually know it.

SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.

A SQL query editor circled by a blue return arrow with a clock, scheduled to come back for review.
Spaced review

Learn it once. Keep it for good.

Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.

Practice, feedback, mastery, review. That's the loop that turns reading into real skill.

Start free

No account, no credit card. Start solving in under a minute.