Calculate Cumulative Sum for Each Integer in Table
Company: Coinbase
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: 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.
You have a table `numbers` containing one integer per row. For each row, return the number and the cumulative sum of all numbers that are less than or equal to that number, ordered by `num`.
Tables
numbers(num INTEGER)
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).