Running Total Revenue

SQL coding challenge · Difficulty: medium · +100 XP

Table: orders

+------------+---------+

| Column     | Type    |

+------------+---------+

| order_id   | INT     |
| salesperson| VARCHAR |
| sale_date  | DATE    |
| amount     | DECIMAL |

+------------+---------+

Problem

-------

For each salesperson, compute a running

(cumulative) total of their revenue,

ordered by sale_date ascending.

Return: salesperson, sale_date, amount,

running_total

Order: salesperson ASC, sale_date ASC

Example Input

-------------

+----+-------+------------+--------+

| id | name  | date       | amount |

+----+-------+------------+--------+

|  1 | Alice | 2024-01-05 | 500.00 |
|  2 | Alice | 2024-01-12 | 300.00 |
|  3 | Alice | 2024-02-01 | 700.00 |
|  4 | Bob   | 2024-01-08 | 400.00 |
|  5 | Bob   | 2024-02-15 | 600.00 |

+----+-------+------------+--------+

Expected Output

+-----------+------------+--------+---------+

| name      | date       | amount | running |

+-----------+------------+--------+---------+

| Alice     | 2024-01-05 | 500.00 |  500.00 |
| Alice     | 2024-01-12 | 300.00 |  800.00 |
| Alice     | 2024-02-01 | 700.00 | 1500.00 |
| Bob       | 2024-01-08 | 400.00 |  400.00 |
| Bob       | 2024-02-15 | 600.00 | 1000.00 |

+-----------+------------+--------+---------+

Hint

----

SUM(amount) OVER (

PARTITION BY salesperson

ORDER BY sale_date

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW

)