Coding questions · Coinbase
Calculate Cumulative Sum for Each Integer in Table
This question evaluates competency in cumulative aggregation and ordering within SQL and related data-manipulation tools, focusing on calculating running totals across rows. It is commonly asked to measure practical application skills in the Data Manipulation (SQL/Python) domain, testing query formulation, efficiency and aggregation semantics at a practical application level.
numbers
+-----+
| num |
+-----+
| 1 |
| 2 |
| 13 |
| 14 |
| 15 |
+-----+
Scenario
You have a table containing one integer per row; for each row you must output the cumulative sum of all values that are ≤ the current row’s value.
Question
Write SQL that returns each number and the sum of all numbers less than or equal to it.
Hints
Window functions such as SUM() OVER with appropriate ordering solve this in one scan.
Cumulative Sum of Numbers
Table numbers
| column | type | constraints |
|---|---|---|
| num | INTEGER |
Sample data:
| num |
|---|
| 1 |
| 2 |
| 13 |
| 14 |
| 15 |
Expected output
For each row in numbers, output the value and the cumulative sum of all values that are less than or equal to that value, ordered by num.
| cumulative_sum | num |
|---|---|
| 1 | 1 |
| 3 | 2 |
| 16 | 13 |
| 30 | 14 |
| 45 | 15 |
Hints
- Use a window function: SUM(num) OVER with ORDER BY num to compute a running total.
- Use a RANGE frame to include all rows with equal values (≤ current value).