Running Total of Revenue

Hard ⏱ 18 min 33% acceptance ★★★★★ 4.9

For every delivered order, return the order_date, the order amount, and a running (cumulative) total of revenue ordered by date as running_total. Use a window function.

Database Schema

Use these exact table and column names in your query. Open Schema & Data under the editor to preview sample rows.

customers 5 columns

customer_idINTEGERnameTEXTcityTEXTcountryTEXTsignup_dateDATE

products 4 columns

product_idINTEGERproduct_nameTEXTcategoryTEXTpriceREAL

orders 5 columns

order_idINTEGERcustomer_idINTEGERorder_dateDATEamountREALstatusTEXT

order_items 4 columns

order_idINTEGERproduct_idINTEGERquantityINTEGERunit_priceREAL

payments 5 columns

payment_idINTEGERorder_idINTEGERmethodTEXTamountREALpaid_dateDATE

employees 6 columns

employee_idINTEGERnameTEXTdepartmentTEXTmanager_idINTEGERsalaryREALhire_dateDATE

Examples

Example 1

Input

orders table

Output

order_date | amount | running_total
2024-01-05 | 1598 | 1598
2024-01-11 | 5499 | 7097 ...

Explanation

SUM(amount) OVER (ORDER BY order_date) accumulates revenue chronologically.

Constraints

Only Delivered orders.
running_total is cumulative by order_date, then order_id.

Topics

Window FunctionsDateAggregation

Companies

PayPalNetflixAmazon

Hints

Hint 1

Use SUM(amount) OVER (ORDER BY order_date, order_id).

Hint 2

Filter status = 'Delivered' first.

Reveal Official Solution

Try it yourself first — you learn more from a struggle than a spoiler.

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date, order_id) AS running_total
FROM orders
WHERE status = 'Delivered'
ORDER BY order_date, order_id;

Related Problems

← Top Product Category by Sales Each Customer's First Order →

Press Run (or Ctrl+Enter) to execute your query against the sample database.

customers5 cols

customer_idINTEGER
nameTEXT
cityTEXT
countryTEXT
signup_dateDATE

customer_id	name	city	country	signup_date
101	Rahul	Delhi	India	2023-01-12
102	Aman	Mumbai	India	2023-02-03
103	Sara	Bengaluru	India	2023-02-20

products4 cols

product_idINTEGER
product_nameTEXT
categoryTEXT
priceREAL

product_id	product_name	category	price
1	Wireless Mouse	Electronics	799
2	Office Chair	Furniture	5499
3	Notebook	Stationery	149

orders5 cols

order_idINTEGER
customer_idINTEGER
order_dateDATE
amountREAL
statusTEXT

order_id	customer_id	order_date	amount	status
1001	101	2024-01-05	1598	Delivered
1002	102	2024-01-11	5499	Delivered
1003	101	2024-02-02	149	Cancelled

order_items4 cols

order_idINTEGER
product_idINTEGER
quantityINTEGER
unit_priceREAL

order_id	product_id	quantity	unit_price
1001	1	2	799
1002	2	1	5499
1004	3	5	149

payments5 cols

payment_idINTEGER
order_idINTEGER
methodTEXT
amountREAL
paid_dateDATE

payment_id	order_id	method	amount	paid_date
5001	1001	UPI	1598	2024-01-05
5002	1002	Card	5499	2024-01-11
5003	1005	UPI	999	2024-03-02

employees6 cols

employee_idINTEGER
nameTEXT
departmentTEXT
manager_idINTEGER
salaryREAL
hire_dateDATE

employee_id	name	department	manager_id	salary
1	Meera	Analytics	NULL	180000
2	Rohit	Analytics	1	95000
3	Neha	Engineering	1	120000

Loading expected output…