Running total of revenue in 2025
Hard · Online shop database · Window functions, WITH (CTE)
For every delivered order placed in 2025, work out the order total (quantity times unit_price, summed over its items) and a running total: the sum of this order and every earlier one in date order.
Return the order id, order_date, the order total and the running total, both rounded to 2 decimals with round, in that order, sorted by order date.
Expected result
| id | order_date | total | running_total |
|---|---|---|---|
| 1 | 2025-02-03 | 34.96 | 34.96 |
| 2 | 2025-02-14 | 220.98 | 255.94 |
| 3 | 2025-03-01 | 89.5 | 345.44 |
| 5 | 2025-04-05 | 70.99 | 416.43 |
| 6 | 2025-04-18 | 82 | 498.43 |
| 7 | 2025-05-02 | 102.99 | 601.42 |
| 8 | 2025-05-27 | 78 | 679.42 |
| 10 | 2025-06-30 | 82.43 | 761.85 |
| 11 | 2025-07-15 | 119 | 880.85 |
| 12 | 2025-08-08 | 52.88 | 933.73 |
| 13 | 2025-08-21 | 44 | 977.73 |
| 15 | 2025-10-02 | 18.5 | 996.23 |
| 16 | 2025-10-19 | 93.99 | 1090.22 |
| 18 | 2025-11-28 | 39 | 1129.22 |
| 19 | 2025-12-12 | 59.48 | 1188.7 |
| 20 | 2025-12-20 | 96 | 1284.7 |
The online shop tables
customers: 18 rows, first 5 shown
| id | name | city | country | signup_date | phone |
|---|---|---|---|---|---|
| 1 | Ravi Kumar | Bengaluru | India | 2025-01-12 | +91 98450 12345 |
| 2 | Emily Johnson | Austin | USA | 2025-01-25 | +1 512 555 0142 |
| 3 | Hiroshi Sato | Osaka | Japan | 2025-02-08 | NULL |
| 4 | Ana Souza | Sao Paulo | Brazil | 2025-02-19 | +55 11 91234 5678 |
| 5 | Priya Sharma | Mumbai | India | 2025-03-03 | +91 99200 54321 |
order_items: 40 rows, first 5 shown
| id | order_id | product_id | quantity | unit_price |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 22.99 |
| 2 | 1 | 9 | 3 | 3.99 |
| 3 | 2 | 4 | 1 | 189 |
| 4 | 2 | 16 | 2 | 15.99 |
| 5 | 3 | 2 | 1 | 89.5 |
orders: 28 rows, first 5 shown
| id | customer_id | order_date | status |
|---|---|---|---|
| 1 | 1 | 2025-02-03 | delivered |
| 2 | 2 | 2025-02-14 | delivered |
| 3 | 3 | 2025-03-01 | delivered |
| 4 | 1 | 2025-03-22 | cancelled |
| 5 | 4 | 2025-04-05 | delivered |
products: 18 rows, first 5 shown
| id | name | category | price | stock |
|---|---|---|---|---|
| 1 | Wireless Mouse | Electronics | 24.99 | 120 |
| 2 | Mechanical Keyboard | Electronics | 89.5 | 45 |
| 3 | USB-C Hub | Electronics | 39 | 80 |
| 4 | Noise Cancelling Headphones | Electronics | 199 | 25 |
| 5 | Intro to Statistics | Books | 48 | 60 |
-- Write your SQL here